Have Beginning Month in Line Chart Be Month Selected in Slicer

[jslade]

I'm looking to sort my line chart by the As Of Date slicer in the screenshot below.  I'm trying to do a year over year comparision, so I can't really pull the year into the chart. When the user picks 7/15 for the As Of Date slicer I want Jul to be the first month in the line chart with Jan showing as the last month (it is technically 2023).  I am returning 6 mo of data, so this works great when the user picks Jan thru June, but as soon as they pick a Jul, Aug, Sep, etc for the As Of Date it starts sorting incorrectly. 

[jslade · Accepted solution]

I was able to get this to work by creating a few extra columns to my Fact table.  

I created 

MonthName = FORMAT(DATE(1, MONTH(FactDOR[Date]), 1), "MMM")

Month Sort = IF(MONTH(FactDOR[As Of Date]) < 7, MONTH(FactDOR[Date]), IF(MONTH(FactDOR[As Of Date]) >= 7 && MONTH(FactDOR[Date]) >= 7, MONTH(FactDOR[Date]) - 6, MONTH(FactDOR[Date]) + 6))

and then 

Month Sort Name = IF(MONTH(FactDOR[As Of Date]) < 7, FactDOR[MonthName], IF(MONTH(FactDOR[As Of Date]) >= 7 && MONTH(FactDOR[Date]) >= 7, FactDOR[MonthName], FactDOR[MonthName]))

I was then able to sort the Month Sort Name by the Month Sort column and the month selected in my slicer is now the first month in my line/ bar charts.

[jslade]

Here are some screenshots of the data.  I've been playing with my FactDOR table and I got the sort order that I need in the Month Sort column.  However, since it changes for each As Of Date I cannot sort the Month Name by it. 

[v-yadongf-msft]

Hi @jslade ,

Can you share with me some screenshots of your data after hiding sensitive information?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang