Custom Rolling Date Range Filter for Report

[pbrenneise]

Hi folks,

What is an effective way to build a fourteen day rolling date range in a Power BI report, especially when you need it to target a specific date? It needs to be something like the most recent Monday and fourteen days prior. I've found good examples online, which I can use to target the starting date and the end date of this range, but I haven't had any success using something equivalent to DATESBETWEEN or DATESINPERIOD to achieve this. Here's an example I've been working with targeting the most recent Saturday (based on another forum post I found):

Using DAX: 

Most Recent Saturday = TODAY() - WEEKDAY( TODAY() )

https://community.powerbi.com/t5/Desktop/Last-saturday-date/td-p/2412178

Rolling Two Week Filter = DATESBETWEEN('DateTable'[Date Column], [Most Recent Saturday], [Most Recent Saturday]-14)

Essentially, this would need to serve as a report-wide filter. Thank you!

pbrenneise

[amitchandak · Accepted solution]

@pbrenneise , Measure most recent Monday and last 14 days

WTD Today =
var _min = TODAY() -WEEKDAY(TODAY(),2) +1 -14  //Monday week start
var _max = TODAY() -WEEKDAY(TODAY(),2) +1
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

For other days you can refer blog to get week start date

.Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...
https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e...