Help with Count formula and consecutive months hitting sales plan

[Anonymous]

Hey y'all,

Looking for a way to count the number of consecutive months a store has actual sales exceeding their planned sales starting with the previous year period. If Store 103 had actual sales greater than plan in year_period 202405, then take the 1 from exceeded count. If store 103 had actual sales greater than plan in previous year_period (202404), then take the 1 from the previous calculation and add 1 to it. I would like to do this until a store hits a 0 to determine how many months in a row a store has hit their sales plan. For store 103, the number I would be looking for would be 7 as they have had actual sales greater than planned sales for 202311 - 202405 thus far or 7 period.

Once a store has a month where they didn't hit sales plan, I would like the count to stop completely until the next store. If year_period is 202405 and a store did not hit their sales plan, I would like the count to be 0.

Any suggestions on how to do this?

[ManuelBolz]

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

[Anonymous]

Store 104 should be 2 consecutive months in your table. Thanks again.

[Anonymous]

Thanks so much for the feedback! In order for the consecutive month count to start, I need there to be a value of 1 in the "Exceeded_Plan" column for the previous period (202405) If a store did not exceed sales plan last period, they would not have any consecutive periods achieving plan. I am only looking to start the count of consecutive months when the plan was exceeded in the previous period.

Right now, it looks like the count is of consecutive months but not starting with the previous period. I also need the count to stop immediately if a plan was not exceeded at any point and only want to know how many periods in a row from the previous period the plan was exceeded.

Thanks again.