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
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
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
WeekEnd = 'Dim_Date'[Date] + 7 - WEEKDAY(Dim_Date[Date],2)

Week

Create a new measure

Paste the following code in:

Week
Week = CONCATENATE(CONCATENATE([WeekStart]," - "),[WeekEnd])
  • No labels