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
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.
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 :
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?
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
you need to add the year field from your date table to the visuals and then drill down to month level
