DAX dynamically

[Prabha45]

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 Oct. If the user opens in 2023 March it should show data from 2021 to 2023 March. The data should change dynamically.

Thank you.

[v-yueyunzh-msft · Accepted solution]

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]

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