how to write a Measure or a calculated column to calculate Accumulative previous Month row total

[mhrkhader01]

Dear Team,

Greetings

i have the follwing columns

No	Date	Sales	Target	Diff	Accumulative
1	1-Jan-21>>	103904>>	108371>>	-4467>>	0>>
2	1-Feb-21>>	184515>>	101049>>	83466>>	83466>>
3	1-Mar-21>>	175449>>	200000>>	-24551>>	58915>>
4	1-Apr-21>>	193121>>	300000>>	-106879>>	0>>
5	1-May-21>>	182197>>	200000>>	-17803>>	0>>
6	1-Jun-21>>	170824>>	100000>>	70824>>	70824>>
7	1-Jul-21>>	164027>>	200000>>	-35973>>	34851>>
8	1-Aug-21>>	167026>>	200000>>	-32974>>	1877>>
9	1-Sep-21>>	111350>>	200000>>	-88650>>	0>>
10	1-Oct-21>>	188179>>	100000>>	88179>>	88179>>
11	1-Nov-21>>	161467>>	100000>>	61467>>	149646>>
12	1-Dec-21>>	147271>>	250000>>	-102729>>	46917>>
13	1-Jan-22>>	106566>>	250000>>	-143434>>	0>>
14	1-Feb-22>>	182679>>	250000>>	-67321>>	0>>
15	1-Mar-22>>	164760>>	250000>>	-85240>>	0>>

accumulative = accumulative in previous row+ saving in the current row

the scenario is that when i achived more that the target, i need to save the increase more than the target in accumulative column

so may be next month i will achive less that the Target,  i need accumulative column to be deducted for the shortage for current row month, when the value of diff is in minues and there is no more saving it should give (0).like in row number 10

 we have increase in sales more than target, so we need to save that amount in Accumulative column, which is (88179) , but for row 11, since we have increase also, i need to add row number 11increase to the previous row accumulative, how to do that. 

regards

Maher

[rfigtree]

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Cumulative-total-that-starts-over-when-the-to...

[ERD]

Hi @mhrkhader01 ,

I don't know all the prerequisites and how your data looks like, but one of the ways is to use Power Query.

#"Added Custom" = Table.AddColumn(#"Previous step name", "Diff", each [Sales] - [Target]),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Accumulative", each List.Accumulate(
    List.Range(#"Added Custom"[Diff],0,[No]),0,(state,current)
    => if state+current < 0 then 0 else state+current
    ))
in
#"Added Custom2"

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[mhrkhader01]

Dear ERD,

thank you so much. really appreciated.

but...can i have the same thing using DAX Measure in PowerPivot?

Regards

Maher

[mhrkhader01]

Dear ERD,

thanks, can i have the file?

[ERD · Accepted solution]

@mhrkhader01 , can't sent files, but the steps are very simple in PQ.

For Difference column:

1. Click Add Column - Custom Column

2. 

For Accumulative column:

1. Click Add Column - Custom Column

2. 

You can copy/past the code from the previous message, just change the column names if different.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.