2 Weeks range dynamic matrix header filter by date range

[SUMESHKUMAR22]

Hi BI experts,

I'm looking for help about the date filter challenge in power bi. Thanks in advance!

Usecase:
I'm using the WeekStart - WeekEnd as WeekRange in my date table as the column header of a matrix. Meanwhile, I have a date range slicer for the content in matrix. 

What I'm trying to get is to make the 2-weeks range can be also change as per the date slicer.

for e.g, if I select from 3Mar'25 - 21Mar'25 in the date slider the first column should show sum(values) from 3/25 - 15/25 (i.e. last2weeks) and the last column header to be 3/16 - 3/21 (i.e. previous2weeks), though it should be 3/16 - 3/29 in the header as we are taking sun-sat calendar wise but we will not because we are selecting till 3/21 in date range so will calculate till 3/21 for last column. 

thanks!

[amitchandak · Accepted solution]

@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]) +1

fortnight = 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))

[SUMESHKUMAR22]

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 

[amitchandak]

@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

Start of Week Year = [FY Start] - weekday([FY Start],2) +1

[SUMESHKUMAR22]

 

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