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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Murali777
Helper III
3 years ago

Hi Biny,

 

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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