Dynamic Sclier

[Amyries]

Hello,

I have a table that includes the Fiscal Year (FY) field. I want to create a slicer such that when I select FY25, the data and visuals will display both FY24 and FY25. Or select FY24, display FY24 and FY23, and similarly for other years.
Data is here: (I can't attract files or links, just can capture them)

As a newcomer to Power BI, I would greatly appreciate your assistance. Thank you!

[danextian · Accepted solution]

Hi @Amyries 

You will need a disconnected table (without relationship) as using column from a related table will show just what's been selected and then a measure referencing that table/column to be used as a visual filter

FY Filter = 
VAR SelectedFY =
    MAX ( FY[FY Number] )
VAR _FY = {
    "FY" & SelectedFY - 1,
    "FY" & SelectedFY
}
RETURN
    CALCULATE (
        COUNTROWS ( 'Date' ),
        KEEPFILTERS ( TREATAS ( _FY, 'Date'[Fiscal Year] ) )
    )

Please see the attached sample pbix.

[JennyMilf]

Thank you!

[v-junyant-msft]

Hi @Amyries ,

If danextian's reply can help you, please remember to accept his reply as a solution to help the other members find it more quickly. Thank you!

Best Regards,
Dino Tao

[danextian]

Please accept my post as the solution.

[123abc]

To create a slicer that dynamically filters the data to include the selected fiscal year (FY) and its preceding year, you can follow these steps in Power BI:

Please follow steps:

• Go to the Modeling tab and select New Column.

• Use the following DAX formula to create a new column, e.g., ShowFY:

  ShowFY = IF( 'Table'[FY] = "FY25" || 'Table'[FY] = "FY24", "FY25", IF( 'Table'[FY] = "FY24" || 'Table'[FY] = "FY23", "FY24", BLANK() ) )

Replace 'Table' with the name of your table. This column will create a filterable field based on the selected fiscal year and its preceding year.

1. Create a Slicer:

   • Add the new ShowFY column to the Slicer visual in your Power BI report.

2. Link the Slicer to Your Visuals:

   • Ensure that the slicer interacts with all relevant visuals by checking the Edit Interactions settings under the Format tab.

3. Test the Slicer:

   • Select FY25 in the slicer. Your visuals should show data for both FY24 and FY25.
   • Select FY24, and your visuals should show data for both FY23 and FY24.

Let me know if you need further adjustments or explanations!

[JennyMilf]

Thank you, but I Select FY24 as the visual just show data for FY23