Forum Discussion
2 Weeks range dynamic matrix header filter by date range
SUMESHKUMAR22 , check if these columns and measures can help
fortnight
Start of year = eomonth([Date], -1*month([Date]))+1
Start of Week Year = [Start of year] - weekday([Start of year]) +1
Week Start = [Date] - weekday([Date]) +1fortnight = quotient(Datediff([Week Start], [Start of Week Year], day),7)+1
or
fortnight = quotient(Datediff([Start of year], [date], day),7)+1
Year fortnight= year([Start of Week Year])*100 + [fortnight])or
Year fortnight= year([Start of year])*100 + [fortnight])
fortnight rank = RANKX('Date','Date'[Year fortnight],,ASC,Dense) //YYYYWW format
These measures can help
This fortnight = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[fortnight rank]=max('Date'[fortnight rank])))
Last fortnight = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[fortnight rank]=max('Date'[fortnight rank])-1))
Hi amitchandak , Sure will check. Thanks for the prompt response.
I just got the requirement change from endusers & now they are expecting like below SS.
E.g. if they select from 3/30 - 4/19 (as shown example) then the matrix should show 3 columns headers which is showing start of week date for each week as you can see in the mockup. And suppose If the user selects:
3/31/25 – 4/19/25,
then group like this:
- 3/31–4/6 → “Mar 31 ’25”
- 4/7–4/13 → “Apr 7 ’25”
- 4/14–4/19 → “Apr 14 ’25”
So it’s not aligned to a calendar week, but chunks of 7 days from the start of selected range.
Hope you got the dynamic header adjustments.
let me know a approach for this.
thanks
SUMESHKUMAR22 , Change the start of Year to April
if(month([Date])<=3, EOMONTH([Date],-1*(month([Date])+ 9)) ,EOMONTH([Date],-1*month([Date])+3 ) )+1
For Monday Week Start