topic Re: How to add conditions to identify the row value in cumulative calculation DAX? in DAX Commands and Tips

How to add conditions to identify the row value in cumulative calculation DAX?

Chrystal — Thu, 07 Nov 2024 08:11:59 GMT

Dear Key Users,

How could I add addtional conditions to identify the row results in cumulative calculations

As in the Table below mentioned, I need to cumulative the "Qty Delta" sort by the Month level, thats give me the column "**bleep** Qty Delta" in dax,

But I want this dax formula to add condition is if the "**bleep** Qty Delta" is positive, then the next cumulative calculation will take this positive as 0 in the next calculation, like how this will performed in column "Correct **bleep** Delta"

Thanks a lot for your help!

Chrystal

Re: How to add conditions to identify the row value in cumulative calculation DAX?

lbendlin — Thu, 07 Nov 2024 11:45:33 GMT

Conditional accumulation is impossible in DAX. The only way you can do this is in Power Query via List.Accumulate() .

Re: How to add conditions to identify the row value in cumulative calculation DAX?

Anonymous — Tue, 12 Nov 2024 09:44:14 GMT

Thanks for the reply from lbendlin.

Hi Chrystal ,

Create a new column and try the following DAX formula:

Result = VAR Currentmonth='Table'[Month] VAR CurrentQtyDelta='Table'[Qty Delta] VAR PreviousResult=CALCULATE(SUM('Table'[Qty Delta]),FILTER('Table','Table'[Month]<Currentmonth&&'Table'[Qty Delta]<0)) RETURN IF(CurrentQtyDelta>0,CurrentQtyDelta,PreviousResult+CurrentQtyDelta)

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to add conditions to identify the row value in cumulative calculation DAX?

Chrystal — Wed, 13 Nov 2024 07:34:42 GMT

Hi! thanks for your reply, I will check it out!!