Forum Discussion
Cumulative Total with Dynamic Date Variables and Slicers - Line Chart Behavior Question
Yes your Calendar table is ok and yes you need to use time intelligence functions if you want to use the Calendar table.
I rechecked your measures here
and I don't think it is quite wise to use them. Why !!
like this one here with the if condition
you telling the measure, if no month of the selected year of the same month is not selected then continue the running total with the sum of datat from the the next month of the next year...that's why you see the line chart goes beyond Spetember.
So better use TI functions to make things more easier.
Community ChampionHi kephas
I think you need to filter your visual or page over the Current Year.
Without selecting the Current year it is like you asking your measure to cumulate the Values in all Months of all Years together.
Hi Amine,
Yes I have tried that, but filtering for the current year still extends the sum to October, which doesn't make sense since the latest date in the dataset is 9/1/2022. Also, if you look at the #var_SelectedYear measure with no slicer selection, it has a value of 2022, so selecting 2022 in the slicer doesn't change the value calculated by the "Y0" measure.
This also doesn't address why the "Y1" cumulative total stops at September in the graph like expected, but the "Y0" cumulative total does not.
Thanks.
- aj1973
Just noticed that your Calendar table is not good. It contain gaps and that is not recomended when using Time Intelligence functions
you wouldn't need to filter any date (Year)
But first add Date Table to your Model and link it to Data (1 to many)
It will work as you you wish
Hi Amine,
Thanks for the reply. I wasn't able to find an issue with my calendar table. Power BI accepts it as a Date table with no errors, so I don't think there are gaps in it. If there are, can you please show me the issue?
Also, I am not using time intelligence functions in my measures, so that is likely not the issue. Are you saying that it will work better if I use time intelligence functions instead?- aj1973
Yes your Calendar table is ok and yes you need to use time intelligence functions if you want to use the Calendar table.
I rechecked your measures here
and I don't think it is quite wise to use them. Why !!
like this one here with the if condition
you telling the measure, if no month of the selected year of the same month is not selected then continue the running total with the sum of datat from the the next month of the next year...that's why you see the line chart goes beyond Spetember.
So better use TI functions to make things more easier.