topic Re: how to write a Measure or a calculated column to calculate Accumulative previous Month row total in DAX Commands and Tips

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

mhrkhader01 — Fri, 07 May 2021 13:29:24 GMT

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

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

ERD — Fri, 07 May 2021 14:34:21 GMT

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.

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

rfigtree — Fri, 07 May 2021 23:12:17 GMT

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Cumulative-total-that-starts-over-when-the-total-is-negative/m-p/1702735#M35060

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

mhrkhader01 — Sat, 08 May 2021 02:03:31 GMT

Dear ERD,

thanks, can i have the file?

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

mhrkhader01 — Tue, 11 May 2021 01:13:04 GMT

Dear ERD,

thank you so much. really appreciated.

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

Regards

Maher

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

ERD — Tue, 11 May 2021 05:47:41 GMT

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

For Difference column:

1. Click Add Column - Custom Column

For Accumulative column:

1. Click Add Column - Custom Column

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.