True/ False statement for Last 3 years with time lag of 90 days - how to DAX

[Dave1972]

Hi all,

I have 2 questions on how to created the following DAX True/False statement. If I want to filter create filter on data for last year I created the following statement: 

Last 365 days = IF(

DATEDIFF('# Calendar'[Date], TODAY(), DAY) <= 365, "TRUE", "FALSE")

(1) But what If I want to create the true/false statement on last year but with a 90 days delay. So actually "True"should be days between  today - 90 days and taday and -455 days. 

(2) and a more complex one: what if I what to get "True" for a year back with a CalendarQuarter delay. So If it is now Feb 11, 2021, I want dates between 1/1/2020 and 31/12/2020 to give "True" But if it will become Apr 1, 2021, then it should swap to dates between 1/3/2020 and 31/3/2020 for "True" value. 

Thanks for support. D

Thanks Dave

[amitchandak · Accepted solution]

@Dave1972 ,

1) Last 365 days = IF(
DATEDIFF('# Calendar'[Date], TODAY()-90, DAY) <= 365, "TRUE", "FALSE")

2) New column =
var _date = date(year(today()), month(Today())-3, day(today()))
var _month = mod(month(_date,3)
var _qt = Switch(_month , 0, eomonth(_date,0), 1, eomonth(_date,2), 2, eomonth(_date,1))
return
if('# Calendar'[Date] <=_qt && '# Calendar'[Date] >= eomonth(_qt,-12)+1, "TRUE", "FALSE")

[Dave1972]

Hi amitchandak,

Thanks for reply. On point 1) Your answer is where I was stuck also but does not give correct answer. 

This formula gives indeed a 90 day lag to the year ago (so False value is pushed back 90 days to 15/11/2019. However it still gives for the most recent 90 days before today the value "TRUE" and that is what needs to be "False" also (so today only the dates 15/11/2021 till 15/11/2019 should only be "TRUE" and currently gives 12/2/2021 till 15/11/2019 as "TRUE". 

In addition I ideally want future dates to be carrying the "False"value also, which is not the case right now.  

[Dave1972]

Hi amitchandak, @amitchandak 

Thanks for your support. Still hoping to get anser to resolve point 1)... Did you manage to look into this?

On point 1) Your answer is where I was stuck also but does not give correct answer. 

This formula gives indeed a 90 day lag to the year ago (so False value is pushed back 90 days to 15/11/2019. However it still gives for the most recent 90 days before today the value "TRUE" and that is what needs to be "False" also (so today only the dates 15/11/2021 till 15/11/2019 should only be "TRUE" and currently gives 12/2/2021 till 15/11/2019 as "TRUE". 

In addition I ideally want future dates to be carrying the "False"value also, which is not the case right now.