Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
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 -
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.