Adding dynamic columns in date table for last 4 weeks, last 13 weeks, etc to use as filters
My data is at weekly grain and I've setup a custom date table by week. I've included columns such as calendar year, quarter, month, etc, but users would like buttons that filter to last 4 weeks or last 13 weeks rather than adjusting the "between" date slicer.
I'm open to ideas on how to do this (the simpler the better) but my thought is that I could accomplish this by adding columns to the date table, such as "last 4 weeks" where the last 4 weeks are populated and do the same thing in another column for last 13 weeks. But I wouldn't want to manually adjust that every week when the data is refreshed.
Is there some kind of formula that I can put into query editor or write in DAX that identifies the most recent 4 weeks and most recent 13 weeks? And here's the key - NOT last 4 weeks as of TODAY, which is built into Power BI's time intelligence, but is useless if your data is not updated daily. Instead, I need the last 4 weeks of data that is actually in the model. (We have about a 2 week lag)