topic Re: Date Table 12 week period in DAX Commands and Tips

Date Table 12 week period

spandy34 — Mon, 06 May 2024 13:24:58 GMT

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 Anonymous Anonymous tamerj1

Re: Date Table 12 week period

lbendlin — Mon, 06 May 2024 19:00:00 GMT

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

Re: Date Table 12 week period

spandy34 — Mon, 06 May 2024 19:03:07 GMT

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

Re: Date Table 12 week period

lbendlin — Mon, 06 May 2024 19:11:05 GMT

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

Re: Date Table 12 week period

spandy34 — Mon, 06 May 2024 19:15:57 GMT

Is that for the 12 Weeks Previous column ?

Re: Date Table 12 week period

lbendlin — Mon, 06 May 2024 19:18:30 GMT

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

Re: Date Table 12 week period

spandy34 — Mon, 06 May 2024 20:31:53 GMT

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.

Re: Date Table 12 week period

lbendlin — Mon, 06 May 2024 20:57:41 GMT

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.

Re: Date Table 12 week period

spandy34 — Mon, 06 May 2024 20:50:45 GMT

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.

Re: Date Table 12 week period

lbendlin — Mon, 06 May 2024 21:01:29 GMT

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.

Re: Date Table 12 week period

Anonymous — Wed, 15 May 2024 06:04:50 GMT

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

Re: Date Table 12 week period

spandy34 — Thu, 16 May 2024 10:44:16 GMT

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.