cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Have Beginning Month in Line Chart Be Month Selected in Slicer

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.

1 ACCEPTED SOLUTION
Frequent Visitor

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.
3 REPLIES 3
Frequent Visitor

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.
Frequent Visitor

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.

Community Support

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

Best regards,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors