Alternative to WEEKNUM() for setting beginning and end of week?

[pborah]

So I have three measures for which the DAX code is - 

Count Last Week = CALCULATE(COUNT('Reports WeeklyReporting'[evt_rin]),'CALENDAR'[Week Number]=WEEKNUM(TODAY(),2)-1)

Count 2 Weeks Ago = CALCULATE(COUNT('Reports WeeklyReporting'[evt_rin]),'CALENDAR'[Week Number]=WEEKNUM(TODAY(),2)-2)

Count last 8 weeks = CALCULATE(COUNT('Reports WeeklyReporting'[evt_rin]),FILTER('CALENDAR',WEEKNUM('CALENDAR'[Date],2)>=WEEKNUM(TODAY(),2)-9 && WEEKNUM('CALENDAR'[Date],2)<=WEEKNUM(TODAY(),2)-1 && YEAR('CALENDAR'[Date])=YEAR(TODAY())))

In addition to that, I set a relative date filter to "This Year" so that the results are reflecting metrics relevant to this year only.

However these metrics break at the beginning of each year for upto the next 8 weeks. This is because  on the week of Jan 1, there is no "Last Week", or last two weeks or last 8 because of the relative date filter set to current year. Any ideas on how to get around this? I only discovered this early in january of 2022. While "Count Last Week" and "Count 2 Weeks Ago" have resolved on their own since enough time has lapsed, "Count Last Weeks" is still off because it hasn't been 8 weeks into 2022 yet.

For context, the relative date filter is necessary because we have data going back to 2017. So without the relative date filter those WEEKNUM results would include data for all prior years with the same weeknum. A week is defined as from 00:00 Monday to 23:59 Sunday. Unfortunately I cannot share the data. Thanks.

[pborah]

@v-rzhou-msft Why did you accept it as a solution when I'm still testing it out? I posted this question, not you.

[Greg_Deckler · Accepted solution]

@pborah Check out Sequential: Sequential - Microsoft Power BI Community

[pborah]

Thanks @Greg_Deckler ! So I'm using the column version for those unique week numbers/sequentials. However the counts are now incorrect. The DAX code I used for "Count Last Week" in my original question above give me the correct numbers but when I use unique week numbers based on sequentials, it is slightly off. 

Count Last WkTest = 
VAR Max_Week= MAX('CALENDAR'[Unique WeekNum])
VAR CaseCount = CALCULATE(COUNT('Reports WeeklyReporting'[evt_rin]),'CALENDAR'[Unique WeekNum]= Max_Week-1)
RETURN CaseCount

I've tried checking my data and verified the correct count numbers using the original formula from my team members. Am I doing something wrong using the sequentials? Thank you.

[Greg_Deckler]

@pborah Make sure you are using the right 2nd parameter for WEEKNUM in your Sequential calculation?

[pborah]

@Greg_Deckler that worked. I just had the relationship between the calendar table and my fact table set to bidirectional which was causing the error. I changed that and everything works fine. Thanks!

[pborah]

@Greg_Deckler I think I did that right. This is my calendar table. Report requirements indicate week start to be on a Monday.