Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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...
Solved! Go to 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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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.
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
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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?
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Hi Amine,
That makes sense. I will revise my measures. Thank you for your help.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
96 | |
69 | |
45 | |
39 | |
30 |
User | Count |
---|---|
159 | |
98 | |
60 | |
42 | |
42 |