Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All,
I have created a calendar table to work alongside my data table. I want to create a graph that totals the values to a specified year and then show monthly totals.
This is what i am trying to acheive
At present, i have dates that range from 2015 to present. I want to display the total prior to 2022 and the monthly thereafter are shown above. Is this possible anf if so how can i acheive this.
Solved! Go to Solution.
It is possible, yes. You would need to add 2 calculated columns to your Calendar table. 1 for the display value and 1 to sort the display value into the correct order.
First the display value:
Month Year Prior =
IF ( YEAR ( [Date] ) < YEAR ( TODAY() ) - 1, "Prior to " & YEAR ( TODAY() ) - 1, FORMAT ( [Date], "mmm-yy") )
Then the sort order:
Month Year Prior Sort =
IF ( YEAR ( [Date] ) < YEAR ( TODAY() ) - 1, 1, YEAR([Date]) * 100 + MONTH ( [Date] ) )
Make sure to select the [Month Year Prior] column and set the sort by to [Month Year Prior Sort]:
Then add the [Month Year Prior] to your chart.
The column is dynamic, so in 2024 it will show 'Prior to 2023' and grab all those dates.
Hi,
you can obtain this
by adding a calculated column
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
Hi,
you can obtain this
by adding a calculated column
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
thank you @serpiva64 .
It works however would prefer to show Month name and year instead of Year and month number but it works exactly how i wanted it to
It is possible, yes. You would need to add 2 calculated columns to your Calendar table. 1 for the display value and 1 to sort the display value into the correct order.
First the display value:
Month Year Prior =
IF ( YEAR ( [Date] ) < YEAR ( TODAY() ) - 1, "Prior to " & YEAR ( TODAY() ) - 1, FORMAT ( [Date], "mmm-yy") )
Then the sort order:
Month Year Prior Sort =
IF ( YEAR ( [Date] ) < YEAR ( TODAY() ) - 1, 1, YEAR([Date]) * 100 + MONTH ( [Date] ) )
Make sure to select the [Month Year Prior] column and set the sort by to [Month Year Prior Sort]:
Then add the [Month Year Prior] to your chart.
The column is dynamic, so in 2024 it will show 'Prior to 2023' and grab all those dates.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |