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

[amitchandak]

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 -

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

WeekThatStartEveryYear.pbix