Cumulative Total with Dynamic Date Variables and Slicers - Line Chart Behavior Question

[kephas]

Hi,

I am trying to display the Year to Date cumulative total in line and clustered column charts. The issue I am encountering is that my "Current Year" cumulative total is extending past the expected month. However, the "Prior Year" cumulative total is working as expected. 

To make the cumulative total measures work, I have a calendar table with four measures to dynamically update the month and year. If there are slicer selections, the measures use the selections. If there are no slicer selections, the measures use the latest date in the dataset.

Next, the cumulative total variables sum up the underlying data with a Filter(all) condition to limit the results based on the dynamic year/month measures mentioned above. 

The screenshot below shows the graph with the "YTD_Y0Value" and "YTD_Y1Value" cumulative total measures. You can see that the Y1 total stops at September but the Y0 variable extends all the way to December. The latest date in the dataset is 9/1/2022, so the Y0 total should stop at September as well.

If I remove the Y0 measure from the graph, the Y1 total still displays correctly. 

Also note that when put in Cards, both Y0 and Y1 cumulative totals calculate correctly. It is only in graphs that the Y0 measure calculates beyond the expected month.

Is there a way to fix the Y0 cumulative total measure so it stops at September like the Y1 cumulative total?

Please see the attached pbix and sample data below:

PBIX: 
https://drive.google.com/file/d/1jvphWFqjmJx6A3ptOPhajr38NdzO79os/view?usp=share_link

Sample Data:
https://docs.google.com/spreadsheets/d/1_VH923VT6SeBEwLEpvUso87xCJ2twhr3/edit?usp=share_link&ouid=11...

[aj1973]

Hi @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.

[kephas]

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]

@kephas 

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

[kephas]

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 · Accepted solution]

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.

[kephas]

Hi Amine,

That makes sense. I will revise my measures. Thank you for your help.