Forum Discussion
Help with DAX for cummulative data
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
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
So shall I create a new column with month name and year like Month_name + Year in the date table?
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.
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
I don't see a date heirarchy in the date table :
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])))
?Why? what's wrong with what you see?
This my expected output :
The only part wrong in this one is the KUM_LY_AE which should show the cummulative data per month.
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. 😞