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 (2021, 2022 and 2023). Suppose, if we are in year 2024, then that time it will automatically take (2022,2023 and 2024).

 

 

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.

5 Replies

  • 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