cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Relative date filter based on year

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).

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Community Support

Hi @Murali777 ,

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 )
``````

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.

Helper III

Hi Biny,

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

Community Support

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

Helper III

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)

Community Support

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.