Looking for a way to calculate cumulative sum over many columns where the data is somewhat tricky.

[ThingZy]

I've been facing an issue when calculating cumulative sum and looking for alternative ways to do it.(other than unpviot columns)
The requirment is to get cumulative sum of values in all the months over few consecutive years and the issue is that when calculating cumulative sum the values of previous years(month-wise) are to be omitted(subtracted) in order to get the required result.

Here is a pic of the data sample and link to access the data : toffeeshare.com/c/9OUbuKo13W

The target is to showcase the cumulative sum from a ceratin date to a certain date in a 'Stacked area chart' like below :




Knowing that this might require too many calculations to solve and get the required result, any reply is welcome!

[Greg_Deckler]

@ThingZy What is the problem with unpivoting? Otherwise, it gets ugly.

[ThingZy]

@Greg_Deckler 

Hello Greg Deckler
The problem is not with unpivoting. Not sure but to obtain the solution it seems that things might get ugly like you have mentioned('Otherwise, it gets ugly')

I'd appriciate any suggestion

Thanks,

Soumith

PREVIEW

[Greg_Deckler · Accepted solution]

@ThingZy Well, I would unpivot the month columns and rename the Attribute column to Month, then add a column like:

MonthSort = 
  SWITCH('Table'[Month],
    "Jan",1,
    "Feb",2,
    "Mar",3,
    // etc.
  )

This will make a cumulative total super easy. Better Running Total - Microsoft Power BI Community

Otherwise, without unpivoting you are going to have some nasty logic and I don't even know how you are going to construct an x-axis having all of your values in different columns. Ugly.