Forum Discussion

Murali777's avatar
Murali777
Helper III
3 years ago
Solved

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 (...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Murali777's avatar
Murali777
Helper III
3 years ago

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)

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.