Showing results for 
Search instead for 
Did you mean: 

Any Weekday Week - Decoding Date and Calendar 2-5 - Power BI Turning 5 Celebration Series

Problem Description:

A week can start any day of the Week. Power BI Support Week can start on Sunday and Monday, but we need a week that can start on Tuesday OR Friday Or Saturday.


Solution Overview :

In the Case of Sunday and Monday Week, we will use Weekday and weeknum functions to do most of the calculation. In all other cases, we need some calculation to find start of the week and first Week start date for that year to calculate week number.



We have used a simple calculation to reduce or add no of days  based of standard Sunday weekday to get our calculation





Date = CALENDAR(date(2014,01,01),date(2020,12,31))




Sunday Week:



Week Start date(Sun-Sat) = 'Date'[Date]+-1*WEEKDAY('Date'[Date])+1
Week End date(Sun-Sat) = [Week Start date(Sun-Sat)]+6
Weekday(Sun-Sat) = WEEKDAY([Date])
Week of Year(Sun-Sat) = WEEKNUM([Date])




Monday Week:



Week Start date(Mon-Sun) = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date(Mon-Sun) = [Week Start date(Mon-Sun)]+6
Weekday(Mon-Sun) = WEEKDAY([Date],2)
Week of Year(Mon-Sun) = WEEKNUM([Date],2)
Week = if('Date'[Week of Year(Mon-Sun)]<10,'Date'[Year]*10 & 'Date'[Week of Year(Mon-Sun)],'Date'[Year]&'Date'[Week of Year(Mon-Sun)])
Week Rank = RANKX(all('Date'),'Date'[Week Start date(Mon-Sun)],,ASC,Dense)
Week name = [Week Start date(Mon-Sun)] & " to "& [Week End date(Mon-Sun)]




We have created some additional columns for Monday week. The same can be replicated for another weekday’s week. Week, Week Rank(For Time Intelligence) and Week Name.


For Week Intelligence, refer to this blog:

Week Is Not So Weak: WTD, Last WTD, and This Week vs Last Week


Tuesday Week:



Week Start date(Tue-Mon) = 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])<3,WEEKDAY('Date'[Date])+4,WEEKDAY('Date'[Date])-3)
Week End date(Tue-Mon) = 'Date'[Week Start date(Tue-Mon)]+6
Weekday(Tue-Mon) = DATEDIFF([Week Start date(Tue-Mon)],[Date],DAY)+1
Week Year Start(Tue-Mon) = minx(filter('Date',[Year]=EARLIER([Year])),[Week Start date(Tue-Mon)]) 
Week of Year(Tue-Mon) = QUOTIENT(DATEDIFF([Week Year Start(Tue-Mon)],'Date'[Date],DAY),7)+1




Wednesday Week:



Week Start date(Wed-Tue) = 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])<4,WEEKDAY('Date'[Date])+3,WEEKDAY('Date'[Date])-4)
Week End date(Wed-Tue) = 'Date'[Week Start date(Wed-Tue)]+6
Weekday(Wed-Tue)= DATEDIFF([Week Start date(Wed-Tue)],[Date],DAY)+1
Week Year Start(Wed-Tue) = minx(filter('Date',[Year]=EARLIER([Year])),[Week Start date(Wed-Tue)]) 
Week of Year(Wed-Tue) = QUOTIENT(DATEDIFF([Week Year Start(Wed-Tue)],'Date'[Date],DAY),7)+1




Thursday Week:



Week Start date(Thu-Wed) = 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])<5,WEEKDAY('Date'[Date])+2,WEEKDAY('Date'[Date])-5)
Week End date(Thu-Wed) = 'Date'[Week Start date(Thu-Wed)]+6
Weekday(Thu-Wed)= DATEDIFF([Week Start date(Thu-Wed)],[Date],DAY)+1
Week Year Start(Thu-Wed) = minx(filter('Date',[Year]=EARLIER([Year])),[Week Start date(Thu-Wed)]) 
Week of Year(Thu-Wed) = QUOTIENT(DATEDIFF([Week Year Start(Thu-Wed)],'Date'[Date],DAY),7)+1




Friday Week



Week Start date(Fri-Thu) = 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])<6,WEEKDAY('Date'[Date])+1,WEEKDAY('Date'[Date])-6)
Week End date(Fri-Thu) = 'Date'[Week Start date(Fri-Thu)]+6
Weekday(Fri-Thu) = DATEDIFF([Week Start date(Fri-Thu)],[Date],DAY)+1
Week Year Start(Fri-Thu) = minx(filter('Date',[Year]=EARLIER([Year])),[Week Start date(Fri-Thu)]) 
Week of Year(Fri-Thu) = QUOTIENT(DATEDIFF([Week Year Start(Fri-Thu)],'Date'[Date],DAY),7)+1




You can also find a pbix attached to this blog.


My Previous Blogs -

Week Is Not So Weak,

Date Difference Across Table- Direct Query Mode, Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard Time Periods and Comparing Data Across Date Ranges
Connect on LinkedIn

What is your favorite Power BI feature release for September 2023?