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.

[xiaois]

Hi, for all those interested so turns out i just need to use TotalYTD instead in my dax code and ensure that my date and fact tables are connected via proper date columns instead of the original where it is just based on date dimension IDs

[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