Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

amitchandak
Super User
‎06-28-2020 12:34 PM

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.

 

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
Comments
Impactful Individual
‎08-02-2021 08:02 AM

@amitchandak 

 

how about saturday to Friday?

how would i do that?

 

0
Super User
‎08-02-2021 08:58 AM

@vjnvinod , it is there in the file. Missed here

 

Week Start date(Sat-Fri) = 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])<7,WEEKDAY('Date'[Date]),WEEKDAY('Date'[Date])-7)
Week End date(Sat-Fri) = 'Date'[Week Start date(Sat-Fri)]+6
Week Start date(Sat-Fri) = 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])<7,WEEKDAY('Date'[Date]),WEEKDAY('Date'[Date])-7)
Week Year Start(Sat-Fri) = minx(filter('Date',[Year]=EARLIER([Year])),'Date'[Week Start date(Sat-Fri)])
0
Helper V
‎11-12-2022 04:46 AM

I use your article to start the week on the friday with following code:

Week Start date(Fri-Thu) = 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])<6,WEEKDAY('Date'[Date])+1,WEEKDAY('Date'[Date])-6)

 

How can I include time to specify when a week starts on the friday? I want it to start at fridays 17:00 and end the week the friday after at 16:59:59

0
‎04-26-2024 11:08 AM

@amitchandak - could you show me what the Power Query M code would look like for Wednesday - Tuesday?

0