Regra para calcular a média acumulada

[Paulo Rodrigues]

Pessoal boa noite,

preciso de uma ajuda, anexo segue a planilha que consta meus leadtime x data x status, e o indicador de Leadtime ideal é 65 -  medido pela média dos casos finalizados; a partir dessa informação preciso criar uma regra para conseguir identificar quais casos  EM ANDAMENTO deverão ser "desconsiderados" , ou seja, quais casos em andamento deverão ter o status alterado para FINALIZADO a fim de não impactar, permanecendo com a média abaixo do Leadtime ideal (=65).

Na planilha deixei o exemplo do mes de março/2024, em que a media de leadtime total está 75, mas o indicador é medido apenas com os casos FINALIZADOS que a média do Leadtime está= 58, agora que vem a minha dúvida, como criar a regra para que consiga identificar quais casos em andamento, devo finalizar para que eu não ultrapasse o indicador ideal (=65)?

Agradeço antecipadamente o apoio!

Leadtime.xlsx

[Erick Oliveira]

Bom dia, @Paulo Rodrigues.

Criei uma função no Power Query que altera o Status dos "ID" para "Desconsiderar" para que a média  dos valores resulte no Lead Time ideal que deseja.

(values as list, status as list, OS as list, dates as list, LeadtimeIdeal as number) as list =>
    let
        LeadtimeIdeal = LeadtimeIdeal,
        // Gera uma lista com as OS que devem ser desconsideradas
        GerarLista = List.Generate(
            () => [
                Return = values{0},
                i = 0,
                a = 0
            ],
            each [i] < List.Count(values),
            each
                try
                    if dates{[i]} = dates{[i] + 1} then
                        [
                            Return = [Return] + values{[i] + 1},
                            i = [i] + 1,
                            a = [a] + 1
                        ]
                    else
                        [
                            Return = values{[i] + 1},
                            i = [i] + 1,
                            a = 0
                        ]
                    otherwise [
                        i = [i] + 1
                    ],
            each if status{[i]} <> "Finalizado" and ([Return]) / ([a] + 1) <= LeadtimeIdeal then OS{[i]} else false 
        )
    in
        GerarLista

Depois invoquei a função para criar a nova coluna de status:

let
    Fonte = Excel.Workbook(File.Contents(pCaminho), null, true),
    Leadtime_Table = Fonte{[Item = "Leadtime", Kind = "Table"]}[Data],
    TipoAlterado = Table.TransformColumnTypes(
        Leadtime_Table, {{"Data", type date}, {"ID", type text}, {"Status", type text}, {"Leadtime", Int64.Type}}
    ),
    InicioMes = Table.AddColumn(TipoAlterado, "MesAno", each Date.StartOfMonth([Data]), type date),
    LinhasClassificadas = Table.Sort(
        InicioMes, {{"MesAno", Order.Ascending}, {"Status", Order.Descending}, {"Leadtime", Order.Ascending}, {"Data", Order.Ascending}}
    ),
    funcaoInvocada =
        let
            _values = List.Buffer(LinhasClassificadas[Leadtime]),
            _status = List.Buffer(LinhasClassificadas[Status]),
            _OS = List.Buffer(LinhasClassificadas[ID]),
            _dates = List.Buffer(LinhasClassificadas[MesAno]),
            _OSDesconsiderar = List.Select(
                fxLeadtimeIdeal(_values, _status, _OS, _dates, valorIdeal), each _ <> false
            )
        in
            List.Buffer( _OSDesconsiderar ),
    ConlunaCondicional = Table.AddColumn(
        LinhasClassificadas,
        "New Status",
        each if List.Contains(funcaoInvocada, [ID]) then "Desconsiderar" else [Status],
        type text
    )
in
    ConlunaCondicional

Com isso, se você selecionar os Status "Finalizado" e "Desconsiderar", verá que o valor médio de Lead Time será de 65 para todos os meses:

Regra para calcular a média acumulada.pbix