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
Hi Amit,
If you see the SS, the date range filtered : its starting as mon- sun because of that its taking first column as 24march’25 but user wants the first column should be 30march’25..so whatever he selects it will be the start week ..so in this way the dynamic nature is impossible to handle.
Correct? Or any other way ?
Thanks