Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |