Forum Discussion
Custom Rolling Date Range Filter for Report
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:
Anonymous , 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-Power-BI-Turning/ba-p/1187482
https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e6f593958
1 Reply
- amitchandak
Super User
Anonymous , 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-Power-BI-Turning/ba-p/1187482
https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e6f593958