This is an enhancement to the Power BI process documented in How to create relative week column in Power BI? - ORAYLIS
An issue I found was in relation to split weeks at the end of the year being allocated to different numbers. This process resolves that issue so that full weeks (7 days) always appear in the data set.
I believe he tried to fix in the Update portion of his page, but I have found a simpler way within the Power Query itself.
So as to be complete in this documentation, I will outline each step of the process for the DimDate data set.
Parameters - Start Date and End Date
Create a parameter named "Start_Date" and input a calendar generation start date
Do the same for "End_Date"
Power Query - Advanced Editor
Create a new blank query in Power Query
Open the "Advanced Editor"
Paste the following code in
let StartDate = (#"Start_Date"), EndDate = (#"End_Date"), NumberOfDays = Duration.Days(EndDate-StartDate), Dates=List.Dates(StartDate,NumberOfDays+1,#duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}), #"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])), #"Added FY" = Table.AddColumn(#"Inserted Year", "FY", each if Date.Month([Date])<7 then [Year] else [Year]+1), #"Added FY Name" = Table.AddColumn( #"Added FY", "FY Name", each Text.Format("#{0}-#{1}",{[FY]-1,[FY]})), #"Changed Type" = Table.TransformColumnTypes(#"Added FY Name" ,{{"Date", type date}}), #"Added Start of FY" = Table.AddColumn(#"Changed Type", "Start of FY", each #date ([FY]-1,7,1)), #"Added End of FY" = Table.AddColumn( #"Added Start of FY", "End of FY", each #date ([FY],6,30)), #"Changed Type1" = Table.TransformColumnTypes(#"Added End of FY",{{"Start of FY", type date}, {"End of FY", type date}, {"Year", Int64.Type}, {"FY", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each true), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "YearWeek", each Date.Year(Date.AddDays([Date],-Date.DayOfWeek([Date],Day.Monday)))*100+Date.WeekOfYear(Date.AddDays([Date],-Date.DayOfWeek([Date],Day.Monday)))) in #"Added Custom"
Custom Column
If you are just looking for the Added Custom Column code that is different from the Oraylis source (linked above) it is:
Date.Year(Date.AddDays([Date],-Date.DayOfWeek([Date],Day.Monday)))*100+Date.WeekOfYear(Date.AddDays([Date],-Date.DayOfWeek([Date],Day.Monday)))
The difference is that the date now uses the Monday as the origin date of the week, instead of the actual date. This way the week number always has 7 days.
DAX
The second portion of this is to input the DAX functions into the data set
Select the "Data" tab
DAX.WeekCounter
Create a new measure
Paste the following code in:
DAX.WeekCounter = CALCULATE(DISTINCTCOUNT(Dim_Date[YearWeek]),all(Dim_Date),Dim_Date[YearWeek]<=EARLIER(Dim_Date[YearWeek]))
RelativWeek
Create a new measure
Paste the following code in:
RelativWeek = Dim_Date[DAX.WeekCounter]-CALCULATE(sum(Dim_Date[DAX.WeekCounter]),ALL(Dim_Date),TODAY()=Dim_Date[Date])
WeekStart
Create a new measure
Paste the following code in:
WeekStart = Dim_Date[Date] - WEEKDAY(Dim_Date[Date],2) +1
WeekEnd
Create a new measure
Paste the following code in:
WeekEnd = 'Dim_Date'[Date] + 7 - WEEKDAY(Dim_Date[Date],2)
Week
Create a new measure
Paste the following code in:
Week = CONCATENATE(CONCATENATE([WeekStart]," - "),[WeekEnd])