Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jslade
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. 

jslade_0-1665004485825.png

 

1 ACCEPTED SOLUTION
jslade
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.

View solution in original post

3 REPLIES 3
jslade
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.
jslade
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. 

jslade_0-1665076058076.png

jslade_3-1665076149617.png

 

 

jslade_1-1665076079501.png

 

jslade_2-1665076095020.png

 

 

v-yadongf-msft
Community Support
Community Support

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.