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

[Chrystal]

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

[Anonymous · Accepted solution]

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.

[lbendlin]

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

[Chrystal]

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