Dynamic week cycles

[Ekaterina_]

Hello everyone, 

I need some help with the dates in my table which I want to group in 2-week-cycles. I have this table here in which I add new data every day.

Date	Application
25.03.2024	App1
11.03.2024	App1
22.03.2024	APPX
01.03.2024	APPX
27.02.2024	APPX
22.07.2024	APPX
13.03.2024	A.P
04.03.2024	A.P
28.02.2024	A.P
20.02.2024	A.P
31.01.2024	A.P
27.02.2024	A.N
21.02.2024	A.N
14.02.2024	A.N
12.02.2024	A.N
25.03.2024	A.D
22.03.2024	A.D
17.03.2024	A.D

Now I want to create a custom column where I summarize the dates into 2-week-cycles which should be dynamically updated, this means if we are eg in week 33 than week 33 and 32 are cycle 1, 31 and 30 are cycle 2, 29 and 28 are cycle 3 and so one. 

However, when we are now in week 36 then the cycle numbers should adjust as follows: week 36 and 35 are cycle 1, 34 and 33 are cycle 2, 32 and 31 are cycle 3 and so one.

I struggle to find the right formula, can someone help me please?

Thanks in advance!

[dufoq3 · Accepted solution]

Hi @Ekaterina_, I'm not sure if this is what you want to achieve.

You have some missing weeks so I groupped it by two with existing data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdC9CoAwDATgd8ksIXet1FVwlo5C6Tv4/pPiD1Sb9SM5jitFOKoFpTHKIPO+Q+pQBPCUbDXn7VKDp0xq7PVMSJ0itAma79joIKc29UVzMJyt0F1+Sul6IxxE9JDe+2dAXZylHkT6Yz0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Application = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "sk-SK"),
    Ad_YearWeek = Table.AddColumn(ChangedType, "YearWeek", each Date.Year([Date]) * 100 + Date.WeekOfYear([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(Ad_YearWeek, {"YearWeek"}, {{"All", each _, type table}}),
    SortedRows = Table.Sort(#"Grouped Rows",{{"YearWeek", Order.Descending}}),
    RemovedOtherColumns = Table.SelectColumns(SortedRows,{"All"}),
    Transformed = Table.FromList(List.Transform(List.Split(List.Transform(RemovedOtherColumns[All], Table.ToRecords), 2), each Table.FromRecords(List.Combine(_))), Splitter.SplitByNothing(), type table[Combined=table], ExtraValues.Error),
    Ad_Cycle = Table.AddIndexColumn(Transformed, "Cycle", 1, 1, Int64.Type),
    ExpandedCombined = Table.ExpandTableColumn(Ad_Cycle, "Combined", {"Date", "Application", "YearWeek"}, {"Date", "Application", "YearWeek"})
in
    ExpandedCombined

[Ekaterina_]

Yes, this will solve my issue.

Thank you very much! 

[dufoq3]

Glad to hear that 😉 You're welcome.

[Greg_Deckler]

@Ekaterina_ Seems like starting with Date.WeekOfYear wrapped by a Number.Mod using a divisor of 2 would be the place to start.