Relative date filter based on year

[Murali777]

Hi,

I want to show dynamice yearly data for each chart. For that i want to use relative filter option for the future filters. for te below example, we need to show last 3 years data to 3 charts (2021, 2022 and 2023). Suppose, if we are in year 2024, then that time it will automatically take (2022,2023 and 2024).

[v-binbinyu-msft]

Hi @Murali777 ,

Please try below steps:

1. below is my test table

Table:

2. create measure with below dax formula

Measure =
VAR cur_year = 2021
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( YEAR ( cur_date ) > cur_year - 3 && YEAR ( cur_date ) <= cur_year, 1, 0 )

Measure =
VAR cur_year = 2022
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( YEAR ( cur_date ) > cur_year - 3 && YEAR ( cur_date ) <= cur_year, 1, 0 )

Measure =
VAR cur_year = 2023
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( YEAR ( cur_date ) > cur_year - 3 && YEAR ( cur_date ) <= cur_year, 1, 0 )

3. add table visula and add measure to filter pane

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Murali777]

Hi Biny,

Each widget should contains the same year data, but in your example, it is showing previous year data also.

[v-binbinyu-msft]

Hi @Murali777 ,
The sample is according to your descriptions.

If I have misunderstood your needs, please feel free to let me know.

Best regards,
Community Support Team_Binbin Yu

[Murali777]

sorry for the inconveniences, What my requirement is, i want 3 widgets with last years of data (for particular widget should contains respective year data).

1. Widget 1 ( current year is 2023, so the first widget should have data only for year 2021)
2. Widget 2 ( current year is 2023, so the Second widget should have data only for year 2022)

3. Widget 3 ( current year is 2023, so the Third widget should have data only for year 2023)
If we are in 2024 Year as a current year, then the 

1. Widget 1 ( Future year is 2024, so the first widget should have data only for year 2022)
2. Widget 2 ( Future year 2024, so the second widget should have data only for year 2023)

3. Widget 3 ( Future year 2024, so the Third widget should have data only for year 2024)

[v-binbinyu-msft · Accepted solution]

Hi @Murali777 ,

Try below dax formula:

Measure =
VAR cur_year =
    YEAR ( TODAY () )
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( YEAR ( cur_date ) = cur_year - 2, 1, 0 )

Measure2 =
VAR cur_year =
    YEAR ( TODAY () )
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( YEAR ( cur_date ) = cur_year - 1, 1, 0 )

Measure3=
VAR cur_year =
    YEAR ( TODAY () )
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( YEAR ( cur_date ) = cur_year, 1, 0 )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.