Forum Discussion

PowerBiNoob's avatar
PowerBiNoob
Frequent Visitor
9 years ago
Solved

Chart sort order per month

Hi,   I am busy with a couple of charts , the info need to be displayed needs to be per month , but a different kind of sort order is needed. currently there is (Jan,Feb,Mar..) or (Mar,Feb,Jan) , I...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Try adding this column to your Date table and use the Sort By Column on your real month column to use this one.

     

    Month Sort Col = 0 - INT(FORMAT('Dates'[Date],"YYYYMM"))
dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
9 years ago

Sounds like you're doing a rolling 12 months?

 

In that case what I've used in the past is a calendar table that is filtered to only show the rolling 12 month period.  You can't do this natively from the PowerBI GUI, but if you go into "Advanced Editor" and add something similar to the following lines of code, your model will have only the last 365 days worth of data.  Then you display the date as the X-axis and hide the year and it should sort how you want it.

 

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= Date.From(DateTime.LocalNow())),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each [Date] > Date.AddYears(Date.From(DateTime.LocalNow()), -1))

(#"Changed Type" should be renamed to whatever the last Applied Step is in your query.  Also adjust #"Filtered Rows" if you've done other filtering beforehand)

 

 

I would love to hear other recommendations on how to do Rolling 12 month visualizations that refresh on their own (i.e., without having to change the page/visual filter manually each time you refresh the data)