Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Murali777
Helper III
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).

 

Murali777_0-1676025716244.png

 

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

vbinbinyumsft_0-1676272771637.png

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.

View solution in original post

5 REPLIES 5
v-binbinyu-msft
Community Support
Community Support

Hi @Murali777 ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1676253758795.png

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

vbinbinyumsft_1-1676253913798.png

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.

Hi Biny,

 

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

 

Murali777_0-1676266893188.png

 

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

vbinbinyumsft_0-1676267404091.png

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

 

Best regards,
Community Support Team_Binbin Yu

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)

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 )

vbinbinyumsft_0-1676272771637.png

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.