topic Re: Cumulative DAX Calculation with Threshold Not Matching Total When Using Date Filters in DAX Commands and Tips

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

EduardoFonseca — Fri, 04 Apr 2025 18:21:51 GMT

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!

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

lbendlin — Fri, 04 Apr 2025 19:08:49 GMT

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

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

v-hashadapu — Mon, 07 Apr 2025 06:55:39 GMT

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,

MIN(QuantityInPeriod, Limit - TotalAccumulatedBefore) * 0.03

)

VAR = ValueAboveLimit

IF(

TotalAccumulatedToDate > Limit,

IF(

TotalAccumulatedBefore < Limit,

(TotalAccumulatedToDate - Limit) * 0.01,

QuantityInPeriod * 0.01

)

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.

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

v-hashadapu — Thu, 10 Apr 2025 12:09:59 GMT

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.

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

EduardoFonseca — Thu, 10 Apr 2025 13:15:22 GMT

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.

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

v-hashadapu — Fri, 11 Apr 2025 09:41:25 GMT

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.

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

v-hashadapu — Thu, 17 Apr 2025 09:04:23 GMT

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.

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

v-hashadapu — Tue, 22 Apr 2025 06:51:41 GMT

topic Re: Cumulative DAX Calculation with Threshold Not Matching Total When Using Date Filters in DAX Commands and Tips

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

Cenário:

Medida atual:

Emitir:

Pergunta:

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

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

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

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

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

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

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