How to mark date as a Rolling 12 month?

[nleuck_101]

I have a Date table and I want to mark weeks that fall within a rolling 12 months as rolling 12 dates. I want to mark them as 1 if they fall within the rolling 12 months and 0 if not. Any ideas would be greatly appreciated!

Thanks,

[amitchandak]

@nleuck_101 , Try a new column in date table

Week Type =
var _b12= date(year(today())-1, Month(Today()), day(Today()))
var _st = _b12 +-1*WEEKDAY(today() ,2)+1
var _end = today() + 7-1*WEEKDAY(today(),2)
Switch( True(),
[Date] >= _st && [Date] <= _end ,"Week In last rolling 12 month " ,
[Week Name]
)

or

Week Type =
var _st= date(year(today())-1, Month(Today()), day(Today()))
var _end = today()
Switch( True(),
[Date] >= _st && [Date] <= _end ,"date In last rolling 12 month " ,
[Week Name]
)

[nleuck_101 · Accepted solution]

@amitchandak 

Thank you for your reply. I went for a different approach that was different than my original idea. I created a WeekOffset column in my Date table. This way I can filter my measures to be between -52 and 0 weeks.

WeekOffset =

VAR StartOfWeek = DimDate[date_field] - WEEKDAY(DimDate[date_field], 2)+1

VAR StartOfCurrentWeek = TODAY() - WEEKDAY(TODAY(), 2)+1

RETURN

(StartOfWeek - StartOfCurrentWeek) / 7