Displaying Dates

[Anonymous]

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.

[jdbuchanan71 · Accepted 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.

[serpiva64 · Accepted solution]

Hi,

you can obtain this

by adding  a calculated column

YM = 'Date'[Year]&FORMAT('Date'[Month Number],"00")

and then creating a group

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!

[Anonymous]

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

[jdbuchanan71 · Accepted 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.

[Anonymous]

Thanks @jdbuchanan71  this is exactly what i wanted and works.

very much appreciated