Forum Discussion
Visual Filters Based on Field Parameter
I have a field parameter that enables a switch between Calendar Year and Fiscal Year (April 1 - Mar 31), and several visuals thas use the year parameter as a dynamic x axis to show measures values calculated based on CY or FY. Parameters and measures using them all work properly.
My issue is with filtering to only show the years desired. I want to cap the visuals to either CY 2024 or FY 2024 however when I filter out calendar year 2025 it caps the fiscal year data at Dec 2024, and filtering FY 2024 (or not filtering at all) includes 2025 dates in the calendar year parameter selection.
What is the best way to dynamically set a visual filter to change based on the parameter slicer selection?
Hi Anonymous ,
You can try the following steps.
1. create two year columns, one for calendar year and one for fiscal year.CY = YEAR('DAX DateTable'[Date])FY = IF ( 'DAX DateTable'[Date] >= DATE ( YEAR ( 'DAX DateTable'[Date] ), 1, 1 ) && 'DAX DateTable'[Date] <= DATE ( YEAR ( 'DAX DateTable'[Date] ), 3, 31 ), YEAR ( 'DAX DateTable'[Date] ) - 1, YEAR ( 'DAX DateTable'[Date] ) )2. Create field parameters for these two columns.
3. Create a year table and do not create relationships with other tables.
4. Create MEASURE.
MEASURE = IF ( MAX ( 'Parameter'[Parameter] ) = "FY" && MAX ( 'Table'[year] ) = MAX ( 'DAX DateTable'[FY] ), MAX ( 'DAX DateTable'[FY] ), IF ( MAX ( 'Parameter'[Parameter] ) = "CY" && MAX ( 'Table'[year] ) = MAX ( 'DAX DateTable'[CY] ), MAX ( 'DAX DateTable'[CY] ) ) )The final result is shown below.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Anonymous ,
You can try the following steps.
1. create two year columns, one for calendar year and one for fiscal year.CY = YEAR('DAX DateTable'[Date])FY = IF ( 'DAX DateTable'[Date] >= DATE ( YEAR ( 'DAX DateTable'[Date] ), 1, 1 ) && 'DAX DateTable'[Date] <= DATE ( YEAR ( 'DAX DateTable'[Date] ), 3, 31 ), YEAR ( 'DAX DateTable'[Date] ) - 1, YEAR ( 'DAX DateTable'[Date] ) )2. Create field parameters for these two columns.
3. Create a year table and do not create relationships with other tables.
4. Create MEASURE.
MEASURE = IF ( MAX ( 'Parameter'[Parameter] ) = "FY" && MAX ( 'Table'[year] ) = MAX ( 'DAX DateTable'[FY] ), MAX ( 'DAX DateTable'[FY] ), IF ( MAX ( 'Parameter'[Parameter] ) = "CY" && MAX ( 'Table'[year] ) = MAX ( 'DAX DateTable'[CY] ), MAX ( 'DAX DateTable'[CY] ) ) )The final result is shown below.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.