Help with DAX for cummulative data

[newbie9292]

I want to get the running total or cummulative total in the "KUM_LY_AE" column like in "KUM_AE" column but only getting summarized data. This the dax query I am using : 

KUM_LY_AE = CALCULATE (SUM(AE_2020[AuftrEing]),FILTER (ALLSELECTED(Date_dim[Date]),Date_dim[Date] <= MAX (Date_dim[Date])))

I have 2020 data and current year data in different tables and connected these tables with a calendar table.

Please help.

[v-deddai1-msft · Accepted solution]

Hi @newbie9292 ,

Please use the following measure:

KUM_LY_AE = CALCULATE (SUM(AE_2020[AuftrEing]),FILTER (ALLSELECTED(Date_dim[Date]),Date_dim[Date] <= MAX (Date_dim[Date])&&MONTH(Date_dim[Date])<=MONTH(MAX (Date_dim[Date]))))

It works well based on my test data. 

You can also refer to my test pbix.

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

Best Regards,

Dedmon Dai

[aj1973]

Hi @newbie9292 

This column here, from which Table is it added to the visual?

And how the Dim_Date and AE_2020 tables are connected?

[newbie9292]

Hello Amine,

Thanks for your reply.

This is how my model looks like : 

This is how the tables are connected : 

[PaulDBrown]

You need to set the data type for you date column in the fact tables to "date". It looks like they are set to type "whole number".

also make sure the fields in your visuals and slicer all come from the date table.

[newbie9292]

Hello @PaulDBrown ,

Thanks for your reply. Yes the columns are in date format now and the slicers are coming from the date table but still the issue remains.

[aj1973]

Hi @newbie9292 

Can I see how you formated the Date column in AE_2020?

Also please Copy paste the visual into another page and show us.

[newbie9292]

Hello @aj1973 ,

This how my formatted date column looks like:

This is the visual :

When I select 2020 in the slicer, the cummulated value is shown correctly : 

But when I select 2020 and 2021 in the slicer, it messes up the cummulated data for 2020 only : 

[PaulDBrown]

you need to add the year field from your date table to the visuals and then drill down to month level

[aj1973]

Well that's normal because the column Month Name is cumulating the sum for January 2020 and January 2021 (it's an example), meaning your formula is not calculating it right upon the filters.

Maybe use Month Name_Year should solve the issue

[newbie9292]

So shall I create a new column with month name and year like Month_name + Year in the date table?

[aj1973]

Yes,

Do the test if you want by replacing the column Month name by column Date in the Matrix, and then Use date hierarchy to only select Month and Year into your Matrix

[newbie9292]

I don't see a date heirarchy in the date table : 

[aj1973]

It looks like you date column is not formated. It should look like this

Or add Date to rows and then delete Quarter and Day

Or add a new field MMM-YY, if it's easier for you.

[newbie9292]

It looks like this : 

Do I need to change the DAX query : 

Kum_AE = CALCULATE (SUM(AE_Weekly[AuftrEing]),FILTER (ALLSELECTED(Date_dim[Date]),Date_dim[Date] <= MAX (Date_dim[Date])))

?

[aj1973]

Why? what's wrong with what you see?

[newbie9292]

This my expected output : 

The only part wrong in this one is the KUM_LY_AE which should show the cummulative data per month.

[newbie9292]

@aj1973 

I am using this DAX query to calculate the cummulative or running total for the year 2020 : 

KUM_LY_AE = CALCULATE (SUM(Combined[AuftrEing]),FILTER (ALLSELECTED(Date_dim[Date]),YEAR(Date_dim[Date])=2020 && Date_dim[Date] <= MAX (Date_dim[Date])))

But it is not working. 😞

[aj1973]

Hey @newbie9292 

Try something like this