Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
EduardoFonseca
New Member

Cumulative DAX Calculation with Threshold Not Matching Total When Using Date Filters

‎04-04-2025 11:21 AM

 

Olá a todos, estou enfrentando um problema com um cálculo cumulativo no DAX. Meu objetivo é aplicar um limite de limiar ao longo do tempo e calcular taxas diferentes abaixo e acima desse limite.

A medida retorna o total correto quando nenhum filtro é aplicado. No entanto, quando filtro os dados por trimestres, a soma dos trimestres excede o total geral. Isso acontece porque cada período avalia o limite separadamente, em vez de tratá-lo como uma acumulação progressiva ao longo do tempo.

Cenário:

  • Tenho uma tabela de vendas chamada f_sales_plan, onde filtro categorias específicas.

  • Eu também uso uma tabela de calendário chamada d_calendario para filtragem de data.

  • Há um limite fixo de 3.753.424,7, e o cálculo funciona da seguinte maneira:

    • Se a quantidade acumulada estiver abaixo do limite, aplico uma taxa de 0,03.

    • Se a quantidade exceder o limite, aplico 0,01 ao excesso.

    • Preciso que a lógica acumule ao longo do tempo até a data selecionada, respeitando filtros como data e empresa.

      Medida atual:

      Emitir:

      • Sem filtros, o resultado total está correto: 137.975.

      • Mas quando eu divido por trimestre, eu obtenho:

        • T1: 25.741

        • 2º trimestre: 55.046

        • 3º trimestre: 59.784

        • 4º trimestre: 48.150

        • Total de trimestres: 188.721 – o que é incorreto

          Isso acontece porque cada período avalia o limite de forma independente, em vez de acumulá-lo progressivamente ao longo do tempo.

          Dax:

          HE_Cota_CP39_HE =
          VAR Limit = 3753424.7

          -- Total quantity accumulated over time (up to current date context)
          VAR TotalAccumulatedQuantity =
          CALCULATE(
          SUM(f_sales_plan[IC_Use]),
          CONTAINSSTRING(f_sales_plan[Category], "MM D4 Local") ||
          CONTAINSSTRING(f_sales_plan[Category], "MM D5 IC Local") ||
          CONTAINSSTRING(f_sales_plan[Category], "DRAM") ||
          CONTAINSSTRING(f_sales_plan[Category], "RD D5 Local IC Local") ||
          CONTAINSSTRING(f_sales_plan[Category], "e/uMCP") ||
          CONTAINSSTRING(f_sales_plan[Category], "REJECTS") ||
          CONTAINSSTRING(f_sales_plan[Category], "SSD Local"),
          FILTER(
          ALL(d_calendario[Date]),
          d_calendario[Date] <= MAX(d_calendario[Date])
          ),
          VALUES(f_sales_plan[Company]) -- Preserve company filter
          )

          -- Quantity in the current filter context (e.g., quarter)
          VAR QuantityInPeriod =
          CALCULATE(
          SUM(f_sales_plan[IC_Use]),
          CONTAINSSTRING(f_sales_plan[Category], "MM D4 Local") ||
          CONTAINSSTRING(f_sales_plan[Category], "MM D5 IC Local") ||
          CONTAINSSTRING(f_sales_plan[Category], "DRAM") ||
          CONTAINSSTRING(f_sales_plan[Category], "RD D5 Local IC Local") ||
          CONTAINSSTRING(f_sales_plan[Category], "e/uMCP") ||
          CONTAINSSTRING(f_sales_plan[Category], "REJECTS") ||
          CONTAINSSTRING(f_sales_plan[Category], "SSD Local")
          )

          -- Calculation for amount under the limit
          VAR ValueBelowLimit =
          IF(
          TotalAccumulatedQuantity <= Limit,
          QuantityInPeriod * 0.03,
          IF(
          TotalAccumulatedQuantity - QuantityInPeriod < Limit,
          (Limit - (TotalAccumulatedQuantity - QuantityInPeriod)) * 0.03,
          0
          )
          )

          -- Calculation for amount above the limit
          VAR ValueAboveLimit =
          IF(
          TotalAccumulatedQuantity > Limit,
          IF(
          TotalAccumulatedQuantity - QuantityInPeriod < Limit,
          (TotalAccumulatedQuantity - Limit) * 0.01,
          QuantityInPeriod * 0.01
          ),
          0
          )

          RETURN
          ValueBelowLimit + ValueAboveLimit


          Pergunta:

          Como posso ajustar minha medida para que o cálculo respeite a lógica cumulativa com base no limite do início até a data atual, mantendo filtros como empresa e data?

          Qualquer ajuda é muito apreciada – obrigado!

Message 1 of 8
273 Views
0
7 REPLIES 7
v-hashadapu
Community Support
Community Support

Monday

Hi @EduardoFonseca , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

Message 8 of 8
23 Views
0
v-hashadapu
Community Support
Community Support

Thursday

Hi @EduardoFonseca , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

Message 7 of 8
82 Views
0
v-hashadapu
Community Support
Community Support

‎04-10-2025 05:09 AM

Hi @EduardoFonseca , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

Message 6 of 8
169 Views
0
v-hashadapu
Community Support
Community Support

‎04-06-2025 11:55 PM

Hi @EduardoFonseca , Thank you for reaching out to the Microsoft Community Forum.

 

Please try below:
HE_Cota_CP39_HE =

VAR Limit = 3753424.7

 

VAR TotalAccumulatedToDate =

    CALCULATE(

        SUM(f_sales_plan[IC_Use]),

        FILTER(

            ALLSELECTED(d_calendario[Date]),

            d_calendario[Date] <= MAX(d_calendario[Date])

        ),

        CONTAINSSTRING(f_sales_plan[Category], "MM D4 Local") ||

        CONTAINSSTRING(f_sales_plan[Category], "MM D5 IC Local") ||

        CONTAINSSTRING(f_sales_plan[Category], "DRAM") ||

        CONTAINSSTRING(f_sales_plan[Category], "RD D5 Local IC Local") ||

        CONTAINSSTRING(f_sales_plan[Category], "e/uMCP") ||

        CONTAINSSTRING(f_sales_plan[Category], "REJECTS") ||

        CONTAINSSTRING(f_sales_plan[Category], "Local SSD"),

        VALUES(f_sales_plan[Company])

    )

 

VAR = TotalAccumulatedBefore

    CALCULATE(

        SUM(f_sales_plan[IC_Use]),

        FILTER(

            ALLSELECTED(d_calendario[Date]),

            d_calendario[Date] < MIN(d_calendario[Date])

        ),

        CONTAINSSTRING(f_sales_plan[Category], "MM D4 Local") ||

        CONTAINSSTRING(f_sales_plan[Category], "MM D5 IC Local") ||

        CONTAINSSTRING(f_sales_plan[Category], "DRAM") ||

        CONTAINSSTRING(f_sales_plan[Category], "RD D5 Local IC Local") ||

        CONTAINSSTRING(f_sales_plan[Category], "e/uMCP") ||

        CONTAINSSTRING(f_sales_plan[Category], "REJECTS") ||

        CONTAINSSTRING(f_sales_plan[Category], "Local SSD"),

        VALUES(f_sales_plan[Company])

    )

 

VAR QuantityInPeriod =

    CALCULATE(

        SUM(f_sales_plan[IC_Use]),

        CONTAINSSTRING(f_sales_plan[Category], "MM D4 Local") ||

        CONTAINSSTRING(f_sales_plan[Category], "MM D5 IC Local") ||

        CONTAINSSTRING(f_sales_plan[Category], "DRAM") ||

        CONTAINSSTRING(f_sales_plan[Category], "RD D5 Local IC Local") ||

        CONTAINSSTRING(f_sales_plan[Category], "e/uMCP") ||

        CONTAINSSTRING(f_sales_plan[Category], "REJECTS") ||

        CONTAINSSTRING(f_sales_plan[Category], "Local SSD")

    )

 

VAR = ValueBelowLimit

    IF(

        TotalAccumulatedBefore >= Limit,

        0,

        MIN(QuantityInPeriod, Limit - TotalAccumulatedBefore) * 0.03

    )

 

VAR = ValueAboveLimit

    IF(

        TotalAccumulatedToDate > Limit,

        IF(

            TotalAccumulatedBefore < Limit,

            (TotalAccumulatedToDate - Limit) * 0.01,

            QuantityInPeriod * 0.01

        ),

        0

    )

 

RETURN

    ValueBelowLimit + ValueAboveLimit

 

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

Message 3 of 8
196 Views
EduardoFonseca
New Member
In response to v-hashadapu

‎04-10-2025 06:15 AM

Hello! First of all, thank you for the support you’ve been providing. Going back to the issue, I still haven’t been able to find a solution, because whenever I apply any kind of filter, it seems like the calculation gets completely limited to the value of 3,753,424.7, ignoring the rest of the data.

Message 4 of 8
163 Views
0
v-hashadapu
Community Support
Community Support
In response to EduardoFonseca

‎04-11-2025 02:41 AM

Hi @EduardoFonseca , Thank you for reaching out to the Microsoft Community Forum.

 

Please check the attached .pbix file with sample data and working solution for your reference.

 

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

Cumulative DAX.pbix
Message 5 of 8
130 Views
0
lbendlin
Super User
Super User

‎04-04-2025 12:08 PM

Conditional accumulations are impossible in DAX. Your only option is List.Accumulate in Power Query.

Message 2 of 8
264 Views
0

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

View All
"); $(".slidesjs-pagination" ).prependTo(".pagination_sec"); $(".slidesjs-pagination" ).append("
"); $(".slidesjs-play.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-stop.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-pagination" ).append(""); $(".slidesjs-pagination" ).append(""); } catch(e){ } /* End: This code is added by iTalent as part of iTrack COMPL-455 */ $(".slidesjs-previous.slidesjs-navigation").attr('tabindex', '0'); $(".slidesjs-next.slidesjs-navigation").attr('tabindex', '0'); /* start: This code is added by iTalent as part of iTrack 1859082 */ $('.slidesjs-play.slidesjs-navigation').attr('id','playtitle'); $('.slidesjs-stop.slidesjs-navigation').attr('id','stoptitle'); $('.slidesjs-play.slidesjs-navigation').attr('role','tab'); $('.slidesjs-stop.slidesjs-navigation').attr('role','tab'); $('.slidesjs-play.slidesjs-navigation').attr('aria-describedby','tip1'); $('.slidesjs-stop.slidesjs-navigation').attr('aria-describedby','tip2'); /* End: This code is added by iTalent as part of iTrack 1859082 */ }); $(document).ready(function() { if($("#slides .item").length < 2 ) { /* Fixing Single Slide click issue (commented following code)*/ // $(".item").css("left","0px"); $(".item.slidesjs-slide").attr('style', 'left:0px !important'); $(".slidesjs-stop.slidesjs-navigation").trigger('click'); $(".slidesjs-previous").css("display", "none"); $(".slidesjs-next").css("display", "none"); } var items_length = $(".item.slidesjs-slide").length; $(".slidesjs-pagination-item > button").attr("aria-setsize",items_length); $(".slidesjs-next, .slidesjs-pagination-item button").attr("tabindex","-1"); $(".slidesjs-pagination-item button").attr("role", "tab"); $(".slidesjs-previous").attr("tabindex","-1"); $(".slidesjs-next").attr("aria-hidden","true"); $(".slidesjs-previous").attr("aria-hidden","true"); $(".slidesjs-next").attr("aria-label","Next"); $(".slidesjs-previous").attr("aria-label","Previous"); //$(".slidesjs-stop.slidesjs-navigation").attr("role","button"); //$(".slidesjs-play.slidesjs-navigation").attr("role","button"); $(".slidesjs-pagination").attr("role","tablist").attr("aria-busy","true"); $("li.slidesjs-pagination-item").attr("role","list"); $(".item.slidesjs-slide").attr("tabindex","-1"); $(".item.slidesjs-slide").attr("aria-label","item"); /*$(".slidesjs-stop.slidesjs-navigation").on('click', function() { var itemNumber = parseInt($('.slidesjs-pagination-item > a.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); });*/ $(".slidesjs-stop.slidesjs-navigation, .slidesjs-pagination-item > button").on('click keydown', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); var itemNumber = parseInt($('.slidesjs-pagination-item > button.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); }); $(".slidesjs-play.slidesjs-navigation").on('click', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); }); $(".slidesjs-pagination-item button").keyup(function(e){ var keyCode = e.keyCode || e.which; if (keyCode == 9) { e.preventDefault(); $(".slidesjs-stop.slidesjs-navigation").trigger('click').blur(); $("button.active").focus(); } }); $(".slidesjs-play").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-stop").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-play")) { $(".slidesjs-stop").focus(); } } }); $(".slidesjs-stop").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-play").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-stop")) { $(".slidesjs-play").focus(); } } }); $(".slidesjs-pagination-item").keydown(function(e){ switch (e.which){ case 37: //left arrow key $(".slidesjs-previous.slidesjs-navigation").trigger('click'); e.preventDefault(); break; case 39: //right arrow key $(".slidesjs-next.slidesjs-navigation").trigger('click'); e.preventDefault(); break; default: return; } $(".slidesjs-pagination-item button.active").focus(); }); }); // Start This code is added by iTalent as part of iTrack 1859082 $(document).ready(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); $("#tip2").attr("aria-hidden","true").addClass("hidden"); $(".slidesjs-stop.slidesjs-navigation, .slidesjs-play.slidesjs-navigation").attr('title', ''); $("a#playtitle").focus(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").mouseover(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").blur(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#playtitle").mouseleave(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#play").keydown(function(ev){ if (ev.which ==27) { $("#tip1").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); $("a#stoptitle").focus(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").mouseover(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").blur(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").mouseleave(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").keydown(function(ev){ if (ev.which ==27) { $("#tip2").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); }); // End This code is added by iTalent as part of iTrack 1859082
Top Kudoed Authors
View All