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)]
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])
Wednesday Week:
Week Start date(Wed-Tue) = 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])
Thursday Week:
Week Start date(Thu-Wed) = 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])
Friday Week
Week Start date(Fri-Thu) = 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])
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
Be the first to comment