Validação de Contexto para somar Horas

Mateus_Silva · Junho 7

Olá, boa tarde!

Estou enfrentando um desafio com meu modelo de dados na tabela 'fat_Manut_OS' e gostaria de compartilhar com vocês para obter algumas sugestões.

No meu modelo, tenho a Coluna [CP_CONJUNTO_CAVALO], que representa a junção dos códigos da Carreta: Cavala, Carreta e Dolly. Nessa coluna, tenho várias ordens de serviços registradas na coluna [NR_ORDSERV], cada uma com suas respectivas datas de entrada [DH_ENTRADA] e saída [DH_SAIDA]. Meu objetivo é calcular no Power BI a quantidade de horas que esses conjuntos ficaram em manutenção, seguindo algumas regras específicas:

Tenho situações em que mais de uma ordem de serviço ocorre no mesmo período. No entanto, não desejo contabilizar esse tempo duas vezes. Portanto, durante o intervalo em que essas ordens de serviço ocorrem simultaneamente, desejo considerar apenas uma delas.

Exemplo 1: Se tenho o [CP_CONJUNTO_CAVALO] = 1, com a [NR_ORDSERV] = 1 entrando na manutenção às 14:00 de hoje e saindo às 16:00, e a [NR_ORDSERV] = 2 entrando às 15:00 e saindo às 18:00, o tempo de serviço total para este CP é de 4 horas.

Exemplo 2: Se tenho o [CP_CONJUNTO_CAVALO] = 2, com a [NR_ORDSERV] = 3 entrando na manutenção às 14:00 de hoje e saindo às 18:00, e a [NR_ORDSERV] = 4 entrando às 15:00 e saindo às 17:00, o tempo de serviço total para este CP também é de 4 horas.
A justificativa para a ocorrência de ordens de serviço praticamente no mesmo horário é que pode haver manutenções simultâneas em várias partes do conjunto. Como a disponibilidade é calculada olhando para o conjunto como um todo, se uma das partes estiver em manutenção, o conjunto estará indisponível para a logística.

Estarei anexando um modelo baseado no meu para realizar os testes.

Veja o exemplo do modelo: tenho esse CJ02 que em Janeiro possui essas Ordens de Serviços. A O.S = 88269 deveria anular todas as outras, visto que está dentro do intervalo de todas as outras do print. Com isso, gostaria de demonstrar apenas a quantidade de horas dessa ordem de serviço neste recorte.

Modelo Dúvida Disponibilidade Hora.pbix

Erick Oliveira · Junho 12

Bom dia, @Mateus_Silva;

Primeiramente tentei uma abordagem com DAX, porém, para alcançar o resultado correto, a medida não teve um bom desempenho, e os valores só aparecem se tiver um filtro aplicado no conjunto, caso contrário, irá apresentar um erro de memória insuficiente.

Segue a medida:

TotalHoras Full DAX =
VAR _StartTable =
    SUMMARIZE (
        duvida_disponibilidade,
        duvida_disponibilidade[CD_CONJUNTO],
        duvida_disponibilidade[StartDate],
        duvida_disponibilidade[EndDate]
    )
VAR _GenerateDates =
    SUMMARIZE (
        GENERATE (
            _StartTable,
            SELECTCOLUMNS (
                GENERATESERIES ( 0, DATEDIFF ( [StartDate], [EndDate], HOUR ), 1 ),
                "@DateTime",
                    [StartDate] + [Value] / 24
            )
        ),
        [@DateTime],
        duvida_disponibilidade[CD_CONJUNTO],
        duvida_disponibilidade[StartDate],
        duvida_disponibilidade[EndDate]
    )
VAR _AuxiliaryColumns =
    ADDCOLUMNS (
        _GenerateDates,
        "@FollowedDateTime",
            VAR _DateTime = [@DateTime]
            VAR _Conjunto = [CD_CONJUNTO]
            VAR _PreviusStart =
                MAXX (
                    FILTER ( _GenerateDates, [@DateTime] < _DateTime && [CD_CONJUNTO] = _Conjunto ),
                    [@DateTime]
                )
            RETURN
                DATEDIFF ( _PreviusStart, [@DateTime], HOUR )
    )
VAR _GroupingTable =
    ADDCOLUMNS (
        _AuxiliaryColumns,
        "@Group",
            VAR _Conjunto = [CD_CONJUNTO]
            VAR _DateTime = [@DateTime]
            RETURN
                COUNTROWS (
                    FILTER (
                        _AuxiliaryColumns,
                        [CD_CONJUNTO] = _Conjunto
                            && [@DateTime] <= _DateTime
                            && [@FollowedDateTime] > 1
                    )
                ) + 1
    )
VAR _FinalTable =
    ADDCOLUMNS (
        GROUPBY (
            _GroupingTable,
            [CD_CONJUNTO],
            [@Group],
            "@StartDate", MINX ( CURRENTGROUP (), [StartDate] ),
            "@EndDate", MAXX ( CURRENTGROUP (), [EndDate] )
        ),
        "@Hours", DATEDIFF ( [@StartDate], [@EndDate], HOUR )
    )
RETURN
    SUMX ( _FinalTable, [@Hours] )

Nas linhas de NR_ORDSERV, os valores de horas aparecem normal como uma medida simples, porém, os totais não irão somar as horas sobrepostas, considerando só as horas de períodos distintos.

Então levei pro Power Query pra trabalhar a base agrupando as datas sobrepostas, fiz o seguinte tratamento:

let
    Source = Base,
    RemoveSeconds = Table.TransformColumns(
        Source,
        {
            {"StartDate", each DateTime.From(DateTime.ToText(_, "dd-MM-yyyy HH:mm"), "pt-BR"), type datetime},
            {"EndDate", each DateTime.From(DateTime.ToText(_, "dd-MM-yyyy HH:mm"), "pt-BR"), type datetime}
        }
    ),
    ListDateTime = Table.AddColumn(
        RemoveSeconds,
        "ListDateTime",
        each List.DateTimes([StartDate], Duration.TotalMinutes([EndDate] - [StartDate]), #duration(0, 0, 1, 0)),
        type {
            datetime
        }
    ),
    ListDateTime_Expand = Table.ExpandListColumn(ListDateTime, "ListDateTime"),
    GroupDateTime = Table.Group(
        ListDateTime_Expand,
        {"CD_CONJUNTO", "ListDateTime"},
        {
            {"NewStartDate", each List.Min([StartDate]), type datetime},
            {"NewEndDate", each List.Max([EndDate]), type datetime},
            {"ID_DATA", each List.Min([ID_DATA]), type date},
            {"NR_ORDSERV", each Text.Combine(List.Transform(_[NR_ORDSERV], Text.From), "|"), type text}
        }
    ),
    GroupDates = Table.Group(
        GroupDateTime,
        {"CD_CONJUNTO", "NewStartDate", "NewEndDate"},
        {
            {"ID_DATA", each List.Min([ID_DATA]), type date},
            {"NR_ORDSERV", each Text.Combine(List.Distinct(_[NR_ORDSERV]), "|"), type text}
        }
    ),
    ListOS = Table.Buffer(Table.AddColumn(GroupDates, "ListOS", each Text.Split([NR_ORDSERV], "|"), type {text})),
    GroupRows = List.Accumulate(
        Table.ToRecords(ListOS),
        {},
        (state, currentRow) =>
            let
                // Check if the current row can be grouped with any existing group
                existingGroup = List.First(
                    List.Select(
                        state,
                        each
                            List.AnyTrue(
                                List.Transform(
                                    _,
                                    each
                                        let
                                            fxContainsValues = (lista1 as list, lista2 as list) as logical =>
                                                List.NonNullCount(List.Intersect({lista1, lista2})) > 0
                                        in
                                            fxContainsValues(Text.Split(_, "|"), currentRow[ListOS])
                                )
                            )
                    ),
                    null
                ),
                // If there is an existing group, add the current row to that group
                newState =
                    if existingGroup <> null then
                        List.Transform(
                            state, each if _ = existingGroup then List.Combine({_, {currentRow[NR_ORDSERV]}}) else _
                        )
                    else
                        // If there is no existing group, create a new group with the current row
                        List.Combine({state, {{currentRow[NR_ORDSERV]}}})
            in
                newState
    ),
    CombineValues = Table.FromList(
        List.Transform(GroupRows, each Text.Combine(List.Distinct(Text.Split(Text.Combine(_, "|"), "|")), "|")),
        Splitter.SplitByNothing(),
        type table [GroupOS = text]
    ),
    SplitOS = Table.AddColumn(CombineValues, "ListOS", each Text.Split([GroupOS], "|"), type {text}),
    SplitOS_Expand = Table.ExpandListColumn(SplitOS, "ListOS"),
    JoinTables = Table.Join(Table.ExpandListColumn(ListOS, "ListOS"), {"ListOS"}, SplitOS_Expand, {"ListOS"}),
    FinalGroup = Table.Group(
        JoinTables,
        {"CD_CONJUNTO", "GroupOS"},
        {
            {"NewStartDate", each List.Min([NewStartDate]), type datetime},
            {"NewEndDate", each List.Max([NewEndDate]), type datetime},
            {"ID_DATA", each List.Min([ID_DATA]), type date}
        }
    )
in
    FinalGroup

Ao invés de 1 linha para cada OS, criei um agrupamento de OS's que compartilham sobreposição de datas:

Então tive o mesmo resultado da solução anterior, com uma medida bem mais simples e com boa performance, porém com a base agrupada.

Vale ressaltar que dependendo do tamanho da base a atualização do relatório pode demorar um pouco.

Modelo Dúvida Disponibilidade Hora.pbix

Johannes Lopes Viana Santa · Junho 11

Boa noite, Mateus!

Uma dúvida.

Como vc sabe quando terminar a contagem de horas de um conjunto e iniciar uma nova contagem do mesmo conjunto.

Exemplo

Se o CJ02 tivesse somente uma OS que iniciasse a manutenção dia 15/01/2024 09:00 e terminasse dia 16/01/2024 09:00. O tempo desse conjunto seria 24 horas, correto?

Agora se para esse mesmo conjunto fosse aberta uma outra OS dia 20/05/2024 11:00 e terminasse dia 20/05/2024 12:00, qual seria o total de manutenção desse conjunto? 25 horas?

Pergunto isso pq no seu enunciado vc disse que precisa calcular quantidade de horas que esses conjuntos ficaram em manutenção.

Mas eles podem ficar em manutenção em dias diferentes. E aí como vc faz para saber quando terminar um ciclo de contagem de horas de manutenções e iniciar um novo no mesmo conjunto?

Mateus_Silva · Junho 11

9 horas atrás, Johannes Lopes Viana Santa disse:

Boa noite, Mateus!

Uma dúvida.

Como vc sabe quando terminar a contagem de horas de um conjunto e iniciar uma nova contagem do mesmo conjunto.

Exemplo

Se o CJ02 tivesse somente uma OS que iniciasse a manutenção dia 15/01/2024 09:00 e terminasse dia 16/01/2024 09:00. O tempo desse conjunto seria 24 horas, correto?

Agora se para esse mesmo conjunto fosse aberta uma outra OS dia 20/05/2024 11:00 e terminasse dia 20/05/2024 12:00, qual seria o total de manutenção desse conjunto? 25 horas?

Pergunto isso pq no seu enunciado vc disse que precisa calcular quantidade de horas que esses conjuntos ficaram em manutenção.

Mas eles podem ficar em manutenção em dias diferentes. E aí como vc faz para saber quando terminar um ciclo de contagem de horas de manutenções e iniciar um novo no mesmo conjunto?

Olá, bom dia!

@Johannes Lopes Viana Santa, tudo bem?

Segue respostas das dúvidas:

Se o CJ02 tivesse somente uma OS que iniciasse a manutenção dia 15/01/2024 09:00 e terminasse dia 16/01/2024 09:00. O tempo desse conjunto seria 24 horas, correto?

R - Isso.

Ex Se eu tenho o conjunto de 4 veiculos:

CJ01 parado dia 01 11 da manhã até dia 02 11 da manhã - 24 horas
CJ02 parado dia 01 11 da manhã até dia 02 11 da manhã - 24 horas - hora que coincide com outra hora igual dentro do conjunto, deve ser desconsiderada.
CJ03 parado dia 02 das 10 as 11 - 1 hora - desconsiderada porque coincidiu com a outra hora acima
CJ04 parado dia 03 das 9 da manhã até dia 04 as 9 da manhã - 24 horas

Total de horas do conjunto 48 horas.

A operação necessita contabilizar como se o conjunto todo fosse 1 só, e nesse caso, horas que coincidem (e que em tese duplicariam a contagem), fosse desconsiderada.

Agora se para esse mesmo conjunto fosse aberta uma outra OS dia 20/05/2024 11:00 e terminasse dia 20/05/2024 12:00, qual seria o total de manutenção desse conjunto? 25 horas?

R - Isso, se for intervalos diferentes é para contabilizar o tempo.

Pergunto isso pq no seu enunciado vc disse que precisa calcular quantidade de horas que esses conjuntos ficaram em manutenção.

R - Isso mesmo.

Mas eles podem ficar em manutenção em dias diferentes. E aí como vc faz para saber quando terminar um ciclo de contagem de horas de manutenções e iniciar um novo no mesmo conjunto?

R - No caso em questão ele tem que verificar a última O.S do modelo que ele considerou na contagem se ela estiver acabada a próxima O.S do modelo é para ser contabilizada e as O.S que estão dentro do período dessas O.S's não são contabilizadas.

Johannes Lopes Viana Santa · Junho 11

E o que identifica todo o conjunto?

Eu tinha entendido que era o código CJ, mas pelo que vc disse na resposta, cada código desse é uma veículo, e que os 4 códigos juntos formam o conjunto completo, correto?

Existe algum código que identifique esse conjunto completo?

Mateus_Silva · Junho 11

1 hora atrás, Johannes Lopes Viana Santa disse:

E o que identifica todo o conjunto?

Eu tinha entendido que era o código CJ, mas pelo que vc disse na resposta, cada código desse é uma veículo, e que os 4 códigos juntos formam o conjunto completo, correto?

Existe algum código que identifique esse conjunto completo?

Olá, boa tarde!

E o que identifica todo o conjunto?
R- Seria o CD_CONJUNTO

A referência que pretendo utilizar para este cálculo é o campo CD_CONJUNTO, que está presente no arquivo Power BI que forneci em anexo. Abaixo, segue uma captura de tela mostrando como esse campo está estruturado. Embora ele seja montado a partir do CD_VEICULO, o controle que desejo realizar está relacionado ao CD_CONJUNTO, que na realidade representa o conjunto de Carreta, Caçamba e Dolly. Essas regras têm como objetivo monitorar o tempo de indisponibilidade, uma vez que o CD_CONJUNTO é essencialmente o conjunto completo do implemento. Isso me permitirá analisar o tempo em que o veículo está indisponível para operações logísticas, já que a manutenção de qualquer um dos CD_VEICULO afeta toda a disponibilidade do CD_CONJUNTO.

Mateus_Silva · Junho 12

4 horas atrás, Erick Oliveira disse:

Bom dia, @Mateus_Silva;

Primeiramente tentei uma abordagem com DAX, porém, para alcançar o resultado correto, a medida não teve um bom desempenho, e os valores só aparecem se tiver um filtro aplicado no conjunto, caso contrário, irá apresentar um erro de memória insuficiente.

Segue a medida:

TotalHoras Full DAX =
VAR _StartTable =
    SUMMARIZE (
        duvida_disponibilidade,
        duvida_disponibilidade[CD_CONJUNTO],
        duvida_disponibilidade[StartDate],
        duvida_disponibilidade[EndDate]
    )
VAR _GenerateDates =
    SUMMARIZE (
        GENERATE (
            _StartTable,
            SELECTCOLUMNS (
                GENERATESERIES ( 0, DATEDIFF ( [StartDate], [EndDate], HOUR ), 1 ),
                "@DateTime",
                    [StartDate] + [Value] / 24
            )
        ),
        [@DateTime],
        duvida_disponibilidade[CD_CONJUNTO],
        duvida_disponibilidade[StartDate],
        duvida_disponibilidade[EndDate]
    )
VAR _AuxiliaryColumns =
    ADDCOLUMNS (
        _GenerateDates,
        "@FollowedDateTime",
            VAR _DateTime = [@DateTime]
            VAR _Conjunto = [CD_CONJUNTO]
            VAR _PreviusStart =
                MAXX (
                    FILTER ( _GenerateDates, [@DateTime] < _DateTime && [CD_CONJUNTO] = _Conjunto ),
                    [@DateTime]
                )
            RETURN
                DATEDIFF ( _PreviusStart, [@DateTime], HOUR )
    )
VAR _GroupingTable =
    ADDCOLUMNS (
        _AuxiliaryColumns,
        "@Group",
            VAR _Conjunto = [CD_CONJUNTO]
            VAR _DateTime = [@DateTime]
            RETURN
                COUNTROWS (
                    FILTER (
                        _AuxiliaryColumns,
                        [CD_CONJUNTO] = _Conjunto
                            && [@DateTime] <= _DateTime
                            && [@FollowedDateTime] > 1
                    )
                ) + 1
    )
VAR _FinalTable =
    ADDCOLUMNS (
        GROUPBY (
            _GroupingTable,
            [CD_CONJUNTO],
            [@Group],
            "@StartDate", MINX ( CURRENTGROUP (), [StartDate] ),
            "@EndDate", MAXX ( CURRENTGROUP (), [EndDate] )
        ),
        "@Hours", DATEDIFF ( [@StartDate], [@EndDate], HOUR )
    )
RETURN
    SUMX ( _FinalTable, [@Hours] )

Nas linhas de NR_ORDSERV, os valores de horas aparecem normal como uma medida simples, porém, os totais não irão somar as horas sobrepostas, considerando só as horas de períodos distintos.

Então levei pro Power Query pra trabalhar a base agrupando as datas sobrepostas, fiz o seguinte tratamento:

let
    Source = Base,
    RemoveSeconds = Table.TransformColumns(
        Source,
        {
            {"StartDate", each DateTime.From(DateTime.ToText(_, "dd-MM-yyyy HH:mm"), "pt-BR"), type datetime},
            {"EndDate", each DateTime.From(DateTime.ToText(_, "dd-MM-yyyy HH:mm"), "pt-BR"), type datetime}
        }
    ),
    ListDateTime = Table.AddColumn(
        RemoveSeconds,
        "ListDateTime",
        each List.DateTimes([StartDate], Duration.TotalMinutes([EndDate] - [StartDate]), #duration(0, 0, 1, 0)),
        type {
            datetime
        }
    ),
    ListDateTime_Expand = Table.ExpandListColumn(ListDateTime, "ListDateTime"),
    GroupDateTime = Table.Group(
        ListDateTime_Expand,
        {"CD_CONJUNTO", "ListDateTime"},
        {
            {"NewStartDate", each List.Min([StartDate]), type datetime},
            {"NewEndDate", each List.Max([EndDate]), type datetime},
            {"ID_DATA", each List.Min([ID_DATA]), type date},
            {"NR_ORDSERV", each Text.Combine(List.Transform(_[NR_ORDSERV], Text.From), "|"), type text}
        }
    ),
    GroupDates = Table.Group(
        GroupDateTime,
        {"CD_CONJUNTO", "NewStartDate", "NewEndDate"},
        {
            {"ID_DATA", each List.Min([ID_DATA]), type date},
            {"NR_ORDSERV", each Text.Combine(List.Distinct(_[NR_ORDSERV]), "|"), type text}
        }
    ),
    ListOS = Table.Buffer(Table.AddColumn(GroupDates, "ListOS", each Text.Split([NR_ORDSERV], "|"), type {text})),
    GroupRows = List.Accumulate(
        Table.ToRecords(ListOS),
        {},
        (state, currentRow) =>
            let
                // Check if the current row can be grouped with any existing group
                existingGroup = List.First(
                    List.Select(
                        state,
                        each
                            List.AnyTrue(
                                List.Transform(
                                    _,
                                    each
                                        let
                                            fxContainsValues = (lista1 as list, lista2 as list) as logical =>
                                                List.NonNullCount(List.Intersect({lista1, lista2})) > 0
                                        in
                                            fxContainsValues(Text.Split(_, "|"), currentRow[ListOS])
                                )
                            )
                    ),
                    null
                ),
                // If there is an existing group, add the current row to that group
                newState =
                    if existingGroup <> null then
                        List.Transform(
                            state, each if _ = existingGroup then List.Combine({_, {currentRow[NR_ORDSERV]}}) else _
                        )
                    else
                        // If there is no existing group, create a new group with the current row
                        List.Combine({state, {{currentRow[NR_ORDSERV]}}})
            in
                newState
    ),
    CombineValues = Table.FromList(
        List.Transform(GroupRows, each Text.Combine(List.Distinct(Text.Split(Text.Combine(_, "|"), "|")), "|")),
        Splitter.SplitByNothing(),
        type table [GroupOS = text]
    ),
    SplitOS = Table.AddColumn(CombineValues, "ListOS", each Text.Split([GroupOS], "|"), type {text}),
    SplitOS_Expand = Table.ExpandListColumn(SplitOS, "ListOS"),
    JoinTables = Table.Join(Table.ExpandListColumn(ListOS, "ListOS"), {"ListOS"}, SplitOS_Expand, {"ListOS"}),
    FinalGroup = Table.Group(
        JoinTables,
        {"CD_CONJUNTO", "GroupOS"},
        {
            {"NewStartDate", each List.Min([NewStartDate]), type datetime},
            {"NewEndDate", each List.Max([NewEndDate]), type datetime},
            {"ID_DATA", each List.Min([ID_DATA]), type date}
        }
    )
in
    FinalGroup

Ao invés de 1 linha para cada OS, criei um agrupamento de OS's que compartilham sobreposição de datas:

Então tive o mesmo resultado da solução anterior, com uma medida bem mais simples e com boa performance, porém com a base agrupada.

Vale ressaltar que dependendo do tamanho da base a atualização do relatório pode demorar um pouco.

Modelo Dúvida Disponibilidade Hora.pbix 76.15 kB · 1 download

Muito obrigado.

Att.

Entrar

Validação de Contexto para somar Horas

Pergunta

Mateus_Silva

Link para o comentário

Compartilhar em outros sites

6 respostass a esta questão

Posts Recomendados

Erick Oliveira

Link para o comentário

Compartilhar em outros sites

Johannes Lopes Viana Santa

Link para o comentário

Compartilhar em outros sites

Mateus_Silva

Link para o comentário

Compartilhar em outros sites

Johannes Lopes Viana Santa

Link para o comentário

Compartilhar em outros sites

Mateus_Silva

Link para o comentário

Compartilhar em outros sites

Mateus_Silva

Link para o comentário

Compartilhar em outros sites

Faça login para comentar