Forum Discussion

Ekaterina_'s avatar
Ekaterina_
Helper I
2 years ago
Solved

Dynamic week cycles

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.

DateApplication
25.03.2024App1
11.03.2024App1
22.03.2024APPX
01.03.2024APPX
27.02.2024APPX
22.07.2024APPX
13.03.2024A.P
04.03.2024A.P
28.02.2024A.P
20.02.2024A.P
31.01.2024A.P
27.02.2024A.N
21.02.2024A.N
14.02.2024A.N
12.02.2024A.N
25.03.2024A.D
22.03.2024A.D
17.03.2024A.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!

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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

     

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        Glad to hear that 😉 You're welcome.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

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