cancel
Showing results for
Did you mean:

## Week That Resets Yearly - Decoding Date and Calendar 3-5 - Power BI Turning 5 Celebration Series

Problem Description:

The first week should start with the start date of the year and should end with the end date of the year. The next year should again start with the start date of the year. Also, the 53rd week should be merged into the 52nd week.

Solution Overview :

As we have learned how to start a week on any day in my last blog post, we will just find the Weekday of Start of Year and use that to set the First Week that starts with the year’s start date.

Solution:

The Weekday of the year’s start date is calculated, and every week refers to the year’s start date as a reference point to calculate start of week and week no.

Code:

``````Date = CALENDAR(date(2014,01,01),date(2020,12,31))
Week Start Date with Year = var _1 =QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY),7) return STARTOFYEAR('Date'[Date]) + if(_1<52,_1,51)*7
Week No = var _1=  QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY),7)+1 return if(_1<52,_1,52)
Week Day = MOD(DATEDIFF('Date'[Week Start Date with Year],[Date],DAY),7)+1``````

You can also find a pbix attached to this blog.

My Previous Blogs -