Forum Discussion
Displaying Dates
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.
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
YM = 'Date'[Year]&FORMAT('Date'[Month Number],"00")and then creating a groupIf this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
4 Replies
Hi,
you can obtain this
by adding a calculated column
YM = 'Date'[Year]&FORMAT('Date'[Month Number],"00")and then creating a groupIf 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.