Date Table 12 week period

[spandy34]

I have a Date Table which in the 12 Week Period Column creates a new period for every 12 weeks as per the column highlighted.  What the 12 Weeks Period is doing through is going back to 1 at the beginning of the new year so for Date 01 January 2024 the 12 Weeks Period has gone back to 1 but there are not 12 weeks in Period 5.  How do I get the column so it just rolls on without returning back to 1 for a new year?

@danextian @v-heq-msft @v-yaningy-msft @tamerj1 

[lbendlin]

Are you taking the WeekNo from a different table, or is your Date table really called 'Procurement_Delivered_Visit_Date' ?

[spandy34]

The date table is called Procurement_Delivered_Visit_Date' and the WeekNo is the column next to the 12 Week Period column 

[lbendlin]

Change your approach. Convert the Date into an integer, integer divide that by 7, then modulo that by 12 and add 1.

[spandy34]

I don't really understand what you mean . How can I change the Date field to a Integer field ? Can you write the column for the 12 Weeks Period with the rolling numbers as I don't understand. 

[lbendlin]

let
    Source = Table.FromList(List.Dates(#date(2023,9,20),1000,#duration(1,0,0,0)),Splitter.SplitByNothing(), {"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateNumber", each Int64.From([Date]),Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "12WeekPeriod", each Int64.From([DateNumber]/84),Int64.Type)
in
    #"Added Custom1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[spandy34]

Thank you I will try it.  The only issue I have is that I have a lot of reports that use that date table so it may take some time to convert all my reports to point to a new table.

[v-yilong-msft]

Hi  @spandy34 ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

[spandy34 · Accepted solution]

Hi - yes I did.  I created the following 12 Weeks Period column as suggested.

12 Weeks Period = ROUNDUP(DIVIDE('Procurement_Planned_Visit_Date'[WeekNo]+1,12),0)

The column included the WeekNo column.  The WeekNo was initially

"WeekNo", WEEKNUM( [Date] ),

But I changed the WeekNo column to the following and it has worked so I now get continuous week numbers

"Week No", QUOTIENT( datediff(minx(_tab, [Date]),[date],day) ,7)

So now the 12 Week Column covers multiple years if you look at the CalYear column below.

[lbendlin]

please use my updated/simplified code.

Are you expecting these period numbers to restart? If so, what would be the rule for that?  As you are aware years and weeks are not compatible.

[spandy34]

Is that for the 12 Weeks Previous column ? 

[lbendlin]

yes - if you can indicate where the count should start.