topic Re: cumulative sum using Measure DAX in PowerPivot in DAX Commands and Tips

cumulative sum using Measure DAX in PowerPivot

mhrkhader01 — Sat, 08 May 2021 13:00:00 GMT

Dear Team,

Greetings.

can i have the formula for Measure in DAX in PowerPivot for the Last column? one of the active members has solved it using PowerQuery, but is there any formula in DAX Measure ?

thanks

regards

Maher

Re: cumulative sum using Measure DAX in PowerPivot

Fowmy — Sat, 08 May 2021 14:49:35 GMT

mhrkhader01

Can you explain how the column should be calculated?

Re: cumulative sum using Measure DAX in PowerPivot

Jihwan_Kim — Sat, 08 May 2021 17:44:34 GMT

Hi, mhrkhader01

Please check the below picture and the sample pbix file's link down below.

This cannot be the ultimate solution, however, I think, this can be one of the solutions for the situation that shows frequent reset situation (less than or equal to every 5 months).

If the situation needs to be considered at least 12 months, I can write 7 more measures. However, If the situation asks to consider 5 years (60 months and write 55 more measures), for instance, then this is not the efficient solution.

I am not sure how your case looks like, but if your case is looking for the solution to reset like this, I think the every-reset period in the case is not more than 12 months.... it is just my guessing....

All measures are in the sample pbix file, and all steps are numbered in front of each measure.

https://www.dropbox.com/s/6pv0xt3gb1wigw1/mhrkhader01.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: cumulative sum using Measure DAX in PowerPivot

mhrkhader01 — Mon, 10 May 2021 19:34:28 GMT

Dear Team. i will explain how the column is calculated

suppose you work in sales, every month there is a target, for one of the Month, if you exceed the target, then i need to save this increase in Accumulative column. lets take example

1 jan , Sales=103904, Target = 108371, so Diff = Sales - Target. so Diff = 103904-108371=-4467, if Diff >0 then we need to save The Value in Accumulative. but if the value less than 0, i need to subtract the value from previous month accumulative, since this is the first month, i need to add 0 to accumulative column. not negative value, to show that my saving amount is 0.

so accumulative value for Jan = 0.

now lets see Feb

1 Feb, Sales , Sales=184515, Target = 101049, so Diff = Sales - Target. so Diff = 83466, if Diff >0 then we need to save The Value in Accumulative. but if the value less than 0, i need to add 0 to accumulative column.

so accumulative value for Feb= Jan accumulative + Feb Increase = 0 + 83466, so Feb Accumulative = 83466

lets see for March

1 Mar, Sales=175449, Target = 200000, so Diff = Sales - Target. so Diff = -24551, if Diff >0 then we need to save The Value in Accumulative. but if the value less than 0, i need to subtract this value from Accumulative Value if accumulative value is more than 0. so accumulative = Feb Accumulative + March Incrase or decrease = 83466+-24551, so total = 58915.

lets see for 1 Apr, Sales=193121, Target = 200000, so Diff = Sales - Target. so Diff =-106879, if Diff >0 then we need to save The Value in Accumulative. but if the value less than 0, i need to subtract the Value from accumulative column.

so accumulative value for Mar= 58915. so 58915+-106879 = , here the result is less than 0, so we need to show 0, not negative value. so accumulative value for April = 0

lets see for 1 May, Sales=182197, Target = 200000, so Diff = Sales - Target. so Diff =-17803, if Diff >0 then we need to save The Value in Accumulative. but if the value less than 0, i need to subtract the Value from accumulative column.

so accumulative value for Apr= 0. so 0+-17803= , here the result is less than 0, so we need to show 0, not negative value.

lets see for 1 June, Sales=170824, Target = 100000, so Diff = Sales - Target. so Diff =70824, if Diff >0 then we need to save The Value in Accumulative. but if the value less than 0, i need to subtract the Value from accumulative column.

so accumulative value for May= 0. so 0+70824=70824 .

and so on.....