Help with Count formula and consecutive months hitting sales plan

Hello Anonymous ,

you had an interesting problem. I'm not 100% sure whether the data in my example table is exactly correct. But feel free to try out my solution with your data.

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

Replace the first SOURCE STEP with your SOURCE STEP:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVVJbiMxDPyLzzlwX94S5P/fGIpqW8sYARpMd4kqFov07++LgATs9fNC4HoSuyOAVyiUmU4jhNffzwPVD1QEUXkCMfv9gskHxskUGiMNh6kSXlBe0EAyyg4reZrQCaWNp6AJRIeI5n5lxQV1cAWXhrJwwpGVcWWtrwpiQw4mT1KLE7qyMtTHQoysaZmRckJhETAottxcLZW5xVhQyJWVUQquHZaKnLnLz13wk1VRZ9VVoFn2oQ3pmwBVlDxU668yn9DV/tEsihiXilUOjRO5dZ9UUMdJUa+26UV0GUAkoojbbDDSNM1W/mYACWKz0RBWYcPwM+tmgCqDqakTV07pCzboZgAkELXxggbn6G+bp2f9g7EV1jxaZQwYDb6w+sEiJEYqzbjOic1O3lMwn87VI5yxFlLJvs1BA9ALSl0BDedYSwb3IAysW0GgXwS66GmY9xw0csxzynjhYmjaxod7Dh62ZZia246LK/5n75U3NMszns0gjEPPpj2T0NCqDOcu8BAjwEOD9yQ8soEytN6Z5R8wPbGxMQByY58agF/b6D0LDQ3RIjvYB9Wqq39P6PICEpHUCuq+lMgeACd29wKJgHcbsoNTrsMJIp6oj7aYNUnnQtqdUM4uji0LltUC4eK7mhYswrUUh7hsQ70r7W6FYdZxjWtRrzX/bSCeLaRq/SNgoWUnt9eXeRhPk6iedZfqQNYoz+v//gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YEAR_PERIOD = _t, STORE_NO = _t, TOTAL_ACTUAL_SALES = _t, TOTAL_PLANNED_SALES = _t, EXCEEDED_PLAN = _t]),
    Type = Table.TransformColumnTypes(Source,{{"YEAR_PERIOD", Int64.Type}, {"STORE_NO", Int64.Type}, {"TOTAL_ACTUAL_SALES", Currency.Type}, {"TOTAL_PLANNED_SALES", Currency.Type}, {"EXCEEDED_PLAN", Int64.Type}}),

    ColumnDifference = Table.AddColumn(Type, "Sales_Difference", each [TOTAL_ACTUAL_SALES] - [TOTAL_PLANNED_SALES]),
    ColumnExceededPlan = Table.AddColumn(ColumnDifference, "Exceeded_Plan", each if [Sales_Difference] > 0 then 1 else 0),
    Sort = Table.Sort(ColumnExceededPlan,{{"STORE_NO", Order.Ascending}, {"YEAR_PERIOD", Order.Ascending}}),
    Group = Table.Group(Sort, {"STORE_NO"}, {{"Data", each _, type table [Store=Int64.Type, Year_Period=Int64.Type, Actual_Sales=Int64.Type, Planned_Sales=Int64.Type, Sales_Difference=Int64.Type, Exceeded_Plan=Int64.Type]}}),

    ColumnConsecutiveMonths = Table.AddColumn(Group, "Consecutive_Months", each 
        let
            Daten = [Data],
            AddIndex = Table.AddIndexColumn(Daten, "Index", 1, 1, Int64.Type),
            Custom1 = List.Accumulate(
                AddIndex[Exceeded_Plan],
                {0},
                (state, current) => 
                    if current = 0 then {0} & state 
                    else {(List.First(state) + current)} & state
            ),
            CustomTable = Table.FromColumns({AddIndex[Index], List.Reverse(Custom1)}, {"Index", "ConsecutiveCount"}),
            MergedTables = Table.Join(AddIndex, "Index", CustomTable, "Index", JoinKind.Inner)
        in
            List.Max(MergedTables[ConsecutiveCount])
    ),
    Remove = Table.RemoveColumns(ColumnConsecutiveMonths,{"Data"}),
    Type2 = Table.TransformColumnTypes(Remove,{{"Consecutive_Months", Int64.Type}})
in
    Type2

My Result:

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github