Filter a date column to a specific timeframe, and display that timeframe in a Matrix visual

[RustyNails]

Hello, I am working with a table that looks like this:

As you can see the Date field can be any date from the past and any date in the future. Out of this data, we want to only look at the next 10 week time frame, including current week. So for example, today is 5/4/2021. I want to look at all my counts from 5/2/2021 to 7/17/2021 in this form:

This matrix starts from Week 19 because today is 5/4, or the 19th week of the year. Next week, it should start from Week 20, and so on. I already have the columns that bring in the Current Week Start Date and 10th Week End Date as follows:

Current Week Start Date:

VAR TodaysDate = TODAY()

VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))

VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekNum1",WEEKNUM([Date],1))

VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],1))

VAR TodaysWeekNum = WEEKNUM(TodaysDate)

VAR Calendar4 = FILTER(Calendar3,[WeekNum1]=TodaysWeekNum&&[WeekDay1]<5)

VAR FirstWorkingDay = MINX(Calendar4,[Date])

RETURN FORMAT(FirstWorkingDay,"mm/dd/yyyy")

10th Week End Date:

VAR TodaysDate = TODAY()

VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))

VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekNum1",WEEKNUM([Date],1))

VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],1))

VAR TodaysWeekNum = WEEKNUM(TodaysDate)+11

VAR Calendar4 = FILTER(Calendar3,[WeekNum1]=TodaysWeekNum&&[WeekDay1]<5)

VAR FirstWorkingDay = MINX(Calendar4,[Date])

RETURN FORMAT(FirstWorkingDay,"mm/dd/yyyy")

How do I make my Matrix visual stay within this Start and End limits? I wont be able to just use page filter because that will exclude the current week.

@Greg_Deckler  I used some of your time intelligence measures 🙂

[Greg_Deckler · Accepted solution]

@RustyNails Well, if those are actually columns, you could try adding another few columns to return an actual Date for Current Week Start Date (Date) and 10th Week End Date (Date). Same formulas that you have just RETURN FirstWorkingDay without the FORMAT. Then, assuming that you have a Date column in this table, you could do something like:

IsCurrentPeriod = IF([Date] >= [Current Week Start Date (Date)] && [Date] <= [10th Week End Date (Date)],1,0)

Then just add a filter to your visual or page for IsCurrentPeriod equals 1

[RustyNails]

DOH!

So smart! That was indeed the missing piece, and my brain could not think of the if statement. Thank you once again!!!