How to Create Dynamic Slicers switching between Fiscal year and Calencar Year

[LijunChen2]

Hello Friends, 

I crated a power BI dashboard with "Date Table" and other fact data tables. I would like to shows the visuals by either the Calendar Year or Fiscal Year (10/1/prior year to 3/30/current year). The Data Table is generated with 

Date Table = CALENDAR(date(2015,10,1),date(2024,9,30)).  I also added Fiscal Year and Calendar Year, as well month, quarter, etc in the Date Table.  The Date Table is connected to the Fact Table by dates. 

Instead of creating two separate slicers for Calendar year and Fiscal Year (like the two dropdown slicers in the screenshot below). I would like to create one year-type slicer with "Calendar Year" and "Fiscal year". Based on year-type slicer selection, the second slicer will show the specific years: either Fiscal Year or Calendar Year (in either number format or string). 

I consulted ChatGPT wich suggested I created a disconnected table Year Type with a year_type column of 2 row/values: "Calendar Year" and "Fiscal Year".  Then I need to create a Dax formulae to add a dynamic column to the date table like below: 

SelectedYear = 
VAR YearSelection = SELECTEDVALUE('Year Type'[year_type], "Fiscal Year")  // Default to Fiscal Year
RETURN 
    IF(YearSelection = "Fiscal Year", 
        'Date Table'[FiscalYear1],  // Use existing Fiscal Year column
        YEAR('Date Table'[Date])  // Calendar Year
    )

The formula does add the Column "SelectedYear" to my date table, which defaults to FiscalYear1 (a numeric year), and show the correct fiscal year. 

I then used the SelectedYear column in the date table to create the second slicer SelectedYear, as shown in the top screenshot. The default years shown are fiscal years, and it filters the year values correctly.  

However, when I click on Calendar Year in year_type slicer, the SelectedYear slicer is not responding with the Calendar Years, and is not filtering year values by calendar year. 

This approach sounds reasonable. But it does not working out. I would be glad if you have any advice/suggestion on how to solve my problem. 

Lijun  

[v-bmanikante]

Hi @LijunChen2,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[LijunChen2 · Accepted solution]

I have found out using the Field Parameter can solve the problem. Using the parameter paramter to create two slicers. The two will be nested, first select teh year type, and the 2nd, the specific year. 

[LijunChen2]

I just found out that I can build a fields parameter with "fiscal year" and "calendar year", both are in my date table. The field parameter is used to create the first slicer. Then I created the 2nd slicer using the field parameter, and click the shown year type to list the specifc values of the selected year type. Here is the link to the youtube video that shows how to achieve this.  

AT 118: Power BI Create dynamic slicers using field parameters 

[v-prasare]

Hi @LijunChen2 ,
Thanks for reaching out to MS Fabric community support

@Deku, thanks for your prompt response. additionally, try below queries:

SelectedYearMeasure =

VAR YearSelection = SELECTEDVALUE('YEARTYPE'[CALENDARTYPE], "FiscalYear")  

RETURN

    IF(YearSelection = "FiscalYear",

        MAX('Date_Table'[FiscalYear]),  

        MAX('DATE_TABLE'[CALENDARYEAR])  

    )

give this measure if required bro, no need to post the above images and also mention that instead of column create the measure as above. 

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[Deku]

Calculated columns are updated on semantic model refresh. They will not respond to the selection of visuals. You will have to use measures and TREATAS() 

Switch(

SELECTEDVALUE('Year Type'[year_type], "Fiscal Year"), 

"Fiscal Year", 

Calculate(

Something,

Treatas(

values( table[selected year]),

Dates[Fiscal year]

),

"Calandar Year", 

Calculate(

Something,

Treatas(

values( table[selected year]),

Dates[Calandar year]

)

)

)

[LijunChen2]

Thanks, Treatas is new to me. I will figure out how it can be used.

[v-bmanikante]

Hi @LijunChen2,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[v-bmanikante]

Hi @LijunChen2,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[LijunChen2]

Hello @v-bmanikante , I have solved the problem by creating the filters using field parameters.