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: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
414 Views
0
1 ACCEPTED SOLUTION
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.

View solution in original post

Cumulative DAX.pbix
Message 5 of 8
271 Views
0
7 REPLIES 7
v-hashadapu
Community Support
Community Support

‎04-21-2025 11:51 PM

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
164 Views
0
v-hashadapu
Community Support
Community Support

‎04-17-2025 02:04 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 7 of 8
223 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
310 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
337 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
304 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
272 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
405 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All