Forum Discussion

Prabha45's avatar
Prabha45
Helper III
3 years ago
Solved

DAX dynamically

Hi Everyone, I'm having data from 2015 to till date and the user wants to see only the last 3 years data.. I mean if the user opens the report today, the visual should show data from 2020 to 2022 ...
General Comment
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Prabha45 

    Here are the steps you can refer to :

    (1)This is my test data:

    Table = CALENDAR( DATE(2008,2,2) , DATE(2023,6,7))

    (2)We can create a measure:

    Measure = var _date = SELECTEDVALUE( 'Table'[Date])
var _last3_year = YEAR( TODAY())-3
return
IF(_date <=TODAY() && _date >= DATE(_last3_year ,1, 1) ,1 ,0)

    (3)Then we put the measure in the "Filter on this visual" and we can configure it and then we can meet your need:

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Prabha45 You could implement in data refresh to just pull the last 3 years. Alternatively, you could write a Complex Selector like this:

Selector Measure = 
  VAR __Today = TODAY()
  VAR __MinDate = DATE(YEAR(__Today)-3,MONTH(__Today),DAY(__Today))
RETURN
  IF(MAX('Table'[Date]) >= __MinDate,1,0)