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

[amitchandak]

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.

Solution:

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

Calendar:

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

Every_Weekday_cal.pbix