Forum Discussion

FrugalEconomist's avatar
FrugalEconomist
Helper III
9 years ago

Finicky Fiscal Year Sorting

Dear Power BI Community,   As always, thank you for your helpful responses. I'm having trouble now presenting my data in a custom end of year. I believe my DAX calculated formula is correct, but t...
Need Help
Tips and Tricks
a68tbird's avatar
a68tbird
Resolver II
9 years ago

This might not be the most elegant solution, but I think it will work.  

 

First, extract the month number from your date in your Calendar table (Date.Month([Date])).  Then create a custom column in your calendar table that will set a custom sort order:

 

=if [Month Number] >= 9
then ([Month Number] - 9)
else ([Month Number] + 3)

 

Finally, in Data Table view, under the Modeling tab, use Sort by Column and select your new custom sort order. 

 

I believe this should work...haven't actually tested in an application. 

FrugalEconomist's avatar
FrugalEconomist
Helper III
9 years ago

Thanks a68tbird.

 

Thanks for your feedback, but unfortunately it doesn't work because the 'canned' power bi date hierarchy can drill down date type fields into Year, Quarter, Month, and Day. However when I create custom columns, even though I've made the date to date join, it doesn't recognize any of the calcluated columns or measures for some reason :(

  • a68tbird's avatar
    a68tbird
    Resolver II
    9 years ago

    How about in the visulization settings. Under the X-Axis setting, you can set Start and End dates. 

     

    • FrugalEconomist's avatar
      FrugalEconomist
      Helper III
      9 years ago

      What!?!? That's so cool, but it doesn't show up for me :(

      I also cannot change the data type to continuous.