Help Please With Custom Week Number in Power Query

[threw001]

Hi,

Thanks for taking time to read this - I am new to power query and need some help please..

I have created a table in excel which calculates week numbers, where week 1 is first Monday on or after 1st July..

The formula in excel is (where A2 is equal date field) =IF(A2="","",INT((A2-WEEKDAY(A2,2)-DATE(YEAR(A2+188-WEEKDAY(A2,2))-1,6,20))/7))

I already have a query in PowerQuery which creates a list of dates but I am a little stuck with how to add the custom week numbers. How can i recreate this formula in Power Query?

Thanks so much

[ccanetta]

This is the quivalent to WEEKNUM 21 or ISOWEEKNUM that worked for me --> Date.DayOfWeek([Date], Day.Thursday)-1

[Anonymous]

Please look Function for ISO Year/Week number (ISO 8601)) 

NB! Pay attention to offsetindays parameter (by default it equals to zero - it means week starts on Sunday)

[threw001]

@Vera_33 - thank you so much, this worked brilliantly and I have learnt a lot from this..

I am using the below to populate table from 1st July 2019 to 31st December 2024

let
    StartDate        = #date (2019,7,1),
    EndDate          = #date (2024,12,31),
    ListOfDates      = List.Dates(StartDate, DurationDays, #duration(1, 0, 0, 0)),
    DurationDays     = Duration.Days (EndDate - StartDate) + 1,
    TableOfDates     = Table.FromList(ListOfDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    DateColText      = Table.RenameColumns(TableOfDates,{{"Column1", "Date"}}),
    Date             = Table.TransformColumnTypes(DateColText,{{"Date", type date}}),
    DayOfWeek = Table.AddColumn(Date, "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type),
    WeekNumber = Table.AddColumn(DayOfWeek, "WeekNumber", each Number.IntegerDivide(Duration.Days([Date]- #date(Date.Year( Date.AddDays([Date], 188-[Day of Week]))-1,6,20))-[Day of Week],7))
in
    WeekNumber

Sorry just another question - if I wanted to add financial year column with the logic being financial year starts in July if week number is 1 - like below, can you please advise best way to do this?

Date	Week Number	Month-Year	FY
27/06/2022	53	Jun-22	2021-22
28/06/2022	53	Jun-22	2021-22
29/06/2022	53	Jun-22	2021-22
30/06/2022	53	Jun-22	2021-22
01/07/2022	53	Jul-22	2021-22
02/07/2022	53	Jul-22	2021-22
03/07/2022	53	Jul-22	2021-22
04/07/2022	1	Jul-22	2022-23
05/07/2022	1	Jul-22	2022-23
06/07/2022	1	Jul-22	2022-23
07/07/2022	1	Jul-22	2022-23
08/07/2022	1	Jul-22	2022-23
09/07/2022	1	Jul-22	2022-23

Thanks

[Vera_33]

Hi @threw001 

Do you have a logic to do it? Normally go with DAX on calendar, can you check this one?

https://www.mssqltips.com/sqlservertip/7133/compute-fiscal-week-calculations-with-power-bi-and-dax/

[threw001]

Hi @Vera_33 

Thank you very much for coming back to me - I have been able to work it out but will check out the article you cited 

Thanks

[Vera_33 · Accepted solution]

Hi @threw001 

Here is one way, follow the logic of your excel formula, paste the M code in Advanced Editor via a blank query, see the steps afterwards

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDXNzIwMlKK1QFyjVC5xqhcE1SuKSrXDJVrjsq1QOVaonANDVC5SK6KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Day of Week" = Table.AddColumn(#"Changed Type", "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Day of Week", "Custom", each Number.IntegerDivide(Duration.Days([Date]- #date(Date.Year( Date.AddDays([Date], 188-[Day of Week]))-1,6,20))-[Day of Week],7))
in
    #"Added Custom"

[Anonymous]

Hi,

According to your screenshot, the 1st of July is in week 53. 

Am I the only one who is shocked?

[threw001]

Hi,

This is given the conditions I have applied to the table and where my table counts the weeks from the start date (regardless of year).

My table starts from 01/07/2019, my financial year and week also starts on this date (my week always starts on a Monday)