Help displaying visual X-axis when switching between FY and Year

[Anonymous]

Hello Power Bi Community,

I could use some help with axis display when switching between Fiscal and Standard calendars in a Power Bi report.  Ideally, I'd like to preserve date hiearchy.  For reference, I followed Goodly's solution here for the setup below.  I also tried DataZoe's solution here, but was running into some issues too.  

Here are my DAX tables: 

//For Standard Calendar//

Date C =

VAR MinDate = YEAR(MIN('Table1'[Start_Date])) - 1

VAR MaxDate = YEAR(MAX('Table1'[End_Date]))

RETURN ADDCOLUMNS(

    FILTER(

        CALENDARAUTO(),

        YEAR([Date]) >= MinDate &&

        YEAR([Date]) <= MaxDate

    ),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "mmmm"),

    "Month Number", MONTH([Date]),

    "Quarter", FORMAT([Date], "\QQ"),

    "Quarter Number", ROUNDUP(MONTH([Date])/3, 0)

)

//For Fiscal Calendar//

Date F =

VAR MinDate = YEAR(MIN('Table1'[Start_Date])) - 1

VAR MaxDate = YEAR(MAX('Table1'[End_Date]))

RETURN ADDCOLUMNS(

    FILTER(

        CALENDARAUTO(),

        YEAR([Date]) >= MinDate &&

        YEAR([Date]) <= MaxDate

    ),

    "Fiscal Year", IF(MONTH([Date]) >= 7, YEAR([Date]) + 1, YEAR([Date])),

    "FY Month", FORMAT([Date], "mmmm"),

    "FY Month Number", IF(MONTH([Date]) >= 7, MONTH([Date]) - 6, MONTH([Date]) + 6),

    "FY Quarter",

        IF(MONTH([Date]) >= 7 && MONTH([Date]) <= 9, "Q1",

        IF(MONTH([Date]) >= 10 && MONTH([Date]) <= 12, "Q2",

        IF(MONTH([Date]) >= 1 && MONTH([Date]) <= 3, "Q3",

        IF(MONTH([Date]) >= 4 && MONTH([Date]) <= 6, "Q4")))),

    "FY Quarter Number",

        IF(MONTH([Date]) >= 7 && MONTH([Date]) <= 9, 1,

        IF(MONTH([Date]) >= 10 && MONTH([Date]) <= 12, 2,

        IF(MONTH([Date]) >= 1 && MONTH([Date]) <= 3, 3,

        IF(MONTH([Date]) >= 4 && MONTH([Date]) <= 6, 4)))))

//COMBINED DISCONNECTED TABLE -- **Note Visual measures (in parameter table)  use TREATAS function to reference this table** Year, Quarter, and Month slicer shown in the image below in the blue box reference these fields too.//

Date C + F =

UNION(

    SELECTCOLUMNS('Date C',

        "Date", 'Date C'[Date],

        "Year", 'Date C'[Year],

        "Month", 'Date C'[Month],

        "Quarter", 'Date C'[Quarter],

        "Calendar Type", "Standard",

        "Sort", 'Date C'[Month Number] * 1,

        "Sort 2", SWITCH('Date C'[Quarter], "Q1", 1, "Q2", 2, "Q3", 3, "Q4", 4)

    ),

    SELECTCOLUMNS('Date F',

        "Date", 'Date F'[Date],

        "Year", 'Date F'[Fiscal Year],

        "Month", 'Date F'[FY Month],

        "Quarter", 'Date F'[FY Quarter],

        "Calendar Type", "Fiscal",

        "Sort", 'Date F'[FY Month Number] * 13,

        "Sort 2", SWITCH('Date F'[FY Quarter], "Q1", 3, "Q2", 4, "Q3", 1, "Q4", 2)

    )

)  

//Current slicer and visual behavior shown below for Standard Year is as expected //

//Current behavior for Fiscal Year below is not as expected.  Drilling down to Month displays axis correctly, but Quarter and Year are not.  I am trying to show Q1 as July when Fiscal Year is selected.  I also don't want to see two years displayed (each half of the Fiscal Year) -- there should just be one stacked column.  

The business case here is pretty straightforward.  We have reports from the same dataset that are distributed in both calendar and fiscal years. 

I have scoured the internet and forums for a solution, and continue to play around with the DAX to no avail.  If anyone is aware of other solutions than the ones linked above, I'm all ears.  I would urge Power BI devs to consider ways to simplify this in future updates. 

Thank you in advance!   

[lbendlin]

I would use a single Calendar table  with the columns for the Fiscal periods added to the regular period columns.  Then you can use Field Parameters to switch between the two. No need for code.

[Anonymous]

Could you elaborate a little here?  It sounds like you're proposing something similar to Power BI Helpline's solution here .   My first thought was also to use parameters  but I ran into the issue of slicers resetting. 

I was thinking maybe there's a combination method with bookmarks?  I would duplicate the "Year", "Quarter", "Month" slicers so there are 6 total, then change the field values appropiately for each scenario, and finally unhide/hide them based on the Fiscal/Calendar single selection.   

I wasn't sure if sticky slicers were going to be an issue with this approach though, and my preference would have been to avoid bookmarks all together.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...