Cumulative Sum depending on the selected month

[Anonymous]

Greetings everyone,

Here's my problem:

I am using the following database to track some metrics:

Database 

What I would like to have is:

When selecting a month inside the slicer (ex: March), I would have a measure that returns the sum of all months until March (Jan, Feb, Mar).

For now, I’ve selected every month on the slicer to achieve this condition ... but i know that it’s not the best way.

Any ideas?

Best regards

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create column and measure like DAX below.

Column:

YearMonth = VALUE(FORMAT('Table1'[Date],"yyyymm"))    
//put this column into Slicer visual as filter options


Measure:

Cumulative sum= CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1), Table1[Metric] =MAX(Table1[Metric])&& Table1[YearMonth]<= MAX(Table1[YearMonth])))

Best Regards,

Amy 

Community Support Team _ Amy

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

[TomMartens]

Hey @Anonymous ,

first you have to shape your data, meaning pivoting the month/year columns into a single month/year column with additional value column. This step will create rows from columns.

Read about this function here: https://radacad.com/pivot-and-unpivot-with-power-bi

Then you have to transform the month/year column into a real date or datetime column.

After that you can finally use a dedicated Calendar table. Almost everything you need that is related to timeintelligence related calculations is described in this article: https://www.daxpatterns.com/time-patterns/

Hopefully, this provides some new ideas to tackle your challenge.

Regards,

Tom