powerbi

Sorting Months in a Power BI Visual by Fiscal Year and Month


I'm attempting to configure a column chart visual in Power BI Desktop so that the months are sorted and shown by our fiscal year which runs July to June.

The Date table I'm using is created using the following DAX:

Date (Fiscal Year) = 
VAR __startDate = DATE(YEAR(MIN('Master Protocol List'[Site Activation Date])), 7, 1)
VAR __endDate = DATE(IF(MONTH(TODAY())>=7, YEAR(TODAY())+1, YEAR(TODAY())), 6, 30)
VAR __dates = CALENDAR ( __startDate, __endDate )
RETURN
ADDCOLUMNS (
   __dates,
   "Fiscal Year", IF(MONTH([Date])>=7, YEAR([Date])+1, YEAR([Date])),
   "Month Number", MONTH ( [Date] ),
   "Month Name", FORMAT ( [Date], "MMMM" ),
   "Month Short Name", FORMAT([Date], "MMM"),
   "Fiscal Month Sort", IF(MONTH([Date])>=7, YEAR([Date])+1 & "-0" & MONTH([Date])-6, YEAR([Date]) & "-" & MONTH([Date])+6)
)

The Date column is sorted by the Fiscal Month Sort column. Currently, the visual appears like this.

The top level of the X-axis is the shortened form of the fiscal year from the Fiscal Year column, followed by the Month and Year from the Date hierarchy. Displayed like this, the months are sorted as desired. However, I would like for only the fiscal year and month to be displayed i.e. the fiscal year on the top level followed by the months sorted July to June. If I remove the hierarchy year, the months revert to being sorted in alphabetical order. I would also like the months to be displayed in a shortened format i.e Jan, Feb, Mar, etc.

Like this, but sorted July to June.

I cannot find a way to configure or sort my Date table to achieve this outcome. Any suggestions or tips would be greatly appreciated.


Solution

  • I've developed a pattern to deal with such "FY scenario's". The key part is to create a "double-edged" calendar table by this means,

    DATES = 
    ADDCOLUMNS(
        CALENDAR( DATE( 2024, 6, 28 ), DATE( 2024, 8, 12 ) ),
        "Date FY", EDATE( [Date], 6 )
    )
    

    enter image description here

    The original [Date] liaison the calendar table with fact table date column; the transformed [Date FY] column facilitates calculations based on FY. With such a "double-edged" calendar table, even time intelligence funcs can be fitted in without much concern about FY. (So far, I've solved tons of FY use cases; but still I'm gladly open to any challenges with my pattern. It helps improve the pattern.)

    Specifically, I'd append a calculated column this way,

    Month Short Name = FORMAT( [Date FY], "MM. " ) & FORMAT( [Date], "MMM" )
    

    enter image description here

    "Sort by column" functionality is "time bomb" when it comes to complex measures because there exists an inherent side effect of it. Here's the detailed reference on this subject, https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/