Cálculo com intervalo de datas com horas

[Anonymous]

Pessoal, boa noite! Cá estou eu de madrugada solicitando vossa ajuda para um assunto que vem me tirado o sono:

Possuo uma base dados para cáculo onde, dentre outras informações, contém uma coluna para data inicial, hora inicial, data final e hora final onde tiro a diferença entre os dois intervalos e depois somo o total com base num intervalo de datas selecionado pelo usuário. Para o cálculo, é selecionado um período de data pelo usuário e a base deve filtrar as informações dentro desse intervalo, calcular a diferença de horas dentro desse intervalo e depois soma-la. Acontece q o periodo de data nem sempre abrange a data selecionada, cabendo ao power bi fazer a tratativa de filtrar as informações dentro do intervalo solicitado. Para isso, segue um exemplo:

Digamos que o periodo selecionado pelo usuário foi de 15/03/2021 a 18/01/2021.

como informações, temos (lembrando que as informações estão em colunas separadas, sendo data inicial, hora inicial, data final e hora final): 

15/03/2021 00:30 - 16/03/2021 04:30. Diferença de 28:30 horas

16/03/2021 17:30 - 16/03/2021 18:40. Diferença de 01:10 horas

13/03/2021 05:00 - 15/03/2021 08:00. Diferença de 08:00 horas. 

Observe que no último caso a diferença considerada deveria ser de 08:00 , devido o intervalo selecionado. Depois, eu somaria essas horas. No caso, esse total seria de 37:40. Alguma sugestão de como eu poderia fazer para obrer esse resultado no power bi? Desde já agradeço. 

[Syndicate_Admin]

Don't @Fhsantana,

It feels a little confusing in terms of logic, you can combine my steps below and adapt them to your real needs.

Step 1, create a date table for the slicer:

dates = CALENDAR("2020/1/1","2021/12/21")

Step 2, create the following measure:

Date range =
VAR MinDate =
    CALCULATE ( MIN ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR min_date =
    MAX ( 'Table'[Start_date] )
VAR MaxDate =
    CALCULATE ( MAX ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR max_date =
    MAX ( 'Table'[end_date] )
VAR datediff =
    IF (
        MaxDate >= min_date
            && MaxDate <= max_date,
        DATEDIFF ( MaxDate, max_date, SECOND ),
        IF (
            MinDate >= min_date
                && MinDate <= max_date,
            DATEDIFF ( MinDate, max_date, SECOND ),
            DATEDIFF ( min_date, max_date, SECOND )
        )
    )
RETURN
    RIGHT ( "0" & INT ( datediff / 3600 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( ( datediff - INT ( datediff / 3600 ) * 3600 ) / 60 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( MOD ( MOD ( datediff, 3600 ), 60 ) ), 2 )

And finally you will get the following:

In my logic, as shown below (sorry for not being good in the drawing), blue is the start time and the end time of the table, red is the segmentation time, when the segmentation is next time, the time obtained by measure is green, otherwise the time side is blue. (You can modify the if decision according to your actual logic)

I hope it's useful to you!

Best regards

Lucien

[Syndicate_Admin]

Don't @Fhsantana,

It feels a little confusing in terms of logic, you can combine my steps below and adapt them to your real needs.

Step 1, create a date table for the slicer:

dates = CALENDAR("2020/1/1","2021/12/21")

Step 2, create the following measure:

Date range =
VAR MinDate =
    CALCULATE ( MIN ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR min_date =
    MAX ( 'Table'[Start_date] )
VAR MaxDate =
    CALCULATE ( MAX ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR max_date =
    MAX ( 'Table'[end_date] )
VAR datediff =
    IF (
        MaxDate >= min_date
            && MaxDate <= max_date,
        DATEDIFF ( MaxDate, max_date, SECOND ),
        IF (
            MinDate >= min_date
                && MinDate <= max_date,
            DATEDIFF ( MinDate, max_date, SECOND ),
            DATEDIFF ( min_date, max_date, SECOND )
        )
    )
RETURN
    RIGHT ( "0" & INT ( datediff / 3600 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( ( datediff - INT ( datediff / 3600 ) * 3600 ) / 60 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( MOD ( MOD ( datediff, 3600 ), 60 ) ), 2 )

And finally you will get the following:

In my logic, as shown below (sorry for not being good in the drawing), blue is the start time and the end time of the table, red is the segmentation time, when the segmentation is next time, the time obtained by measure is green, otherwise the time side is blue. (You can modify the if decision according to your actual logic)

I hope it's useful to you!

Best regards

Lucien

[Syndicate_Admin]

Don't @Fhsantana,

It feels a little confusing in terms of logic, you can combine my steps below and adapt them to your real needs.

Step 1, create a date table for the slicer:

dates = CALENDAR("2020/1/1","2021/12/21")

Step 2, create the following measure:

Date range =
VAR MinDate =
    CALCULATE ( MIN ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR min_date =
    MAX ( 'Table'[Start_date] )
VAR MaxDate =
    CALCULATE ( MAX ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR max_date =
    MAX ( 'Table'[end_date] )
VAR datediff =
    IF (
        MaxDate >= min_date
            && MaxDate <= max_date,
        DATEDIFF ( MaxDate, max_date, SECOND ),
        IF (
            MinDate >= min_date
                && MinDate <= max_date,
            DATEDIFF ( MinDate, max_date, SECOND ),
            DATEDIFF ( min_date, max_date, SECOND )
        )
    )
RETURN
    RIGHT ( "0" & INT ( datediff / 3600 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( ( datediff - INT ( datediff / 3600 ) * 3600 ) / 60 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( MOD ( MOD ( datediff, 3600 ), 60 ) ), 2 )

And finally you will get the following:

In my logic, as shown below (sorry for not being good in the drawing), blue is the start time and the end time of the table, red is the segmentation time, when the segmentation is next time, the time obtained by measure is green, otherwise the time side is blue. (You can modify the if decision according to your actual logic)

I hope it's useful to you!

Best regards

Lucien

[Syndicate_Admin]

Don't @Fhsantana,

It feels a little confusing in terms of logic, you can combine my steps below and adapt them to your real needs.

Step 1, create a date table for the slicer:

dates = CALENDAR("2020/1/1","2021/12/21")

Step 2, create the following measure:

Date range =
VAR MinDate =
    CALCULATE ( MIN ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR min_date =
    MAX ( 'Table'[Start_date] )
VAR MaxDate =
    CALCULATE ( MAX ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR max_date =
    MAX ( 'Table'[end_date] )
VAR datediff =
    IF (
        MaxDate >= min_date
            && MaxDate <= max_date,
        DATEDIFF ( MaxDate, max_date, SECOND ),
        IF (
            MinDate >= min_date
                && MinDate <= max_date,
            DATEDIFF ( MinDate, max_date, SECOND ),
            DATEDIFF ( min_date, max_date, SECOND )
        )
    )
RETURN
    RIGHT ( "0" & INT ( datediff / 3600 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( ( datediff - INT ( datediff / 3600 ) * 3600 ) / 60 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( MOD ( MOD ( datediff, 3600 ), 60 ) ), 2 )

And finally you will get the following:

In my logic, as shown below (sorry for not being good in the drawing), blue is the start time and the end time of the table, red is the segmentation time, when the segmentation is next time, the time obtained by measure is green, otherwise the time side is blue. (You can modify the if decision according to your actual logic)

I hope it's useful to you!

Best regards

Lucien

[Syndicate_Admin]

Don't @Fhsantana,

It feels a little confusing in terms of logic, you can combine my steps below and adapt them to your real needs.

Step 1, create a date table for the slicer:

dates = CALENDAR("2020/1/1","2021/12/21")

Step 2, create the following measure:

Date range =
VAR MinDate =
    CALCULATE ( MIN ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR min_date =
    MAX ( 'Table'[Start_date] )
VAR MaxDate =
    CALCULATE ( MAX ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR max_date =
    MAX ( 'Table'[end_date] )
VAR datediff =
    IF (
        MaxDate >= min_date
            && MaxDate <= max_date,
        DATEDIFF ( MaxDate, max_date, SECOND ),
        IF (
            MinDate >= min_date
                && MinDate <= max_date,
            DATEDIFF ( MinDate, max_date, SECOND ),
            DATEDIFF ( min_date, max_date, SECOND )
        )
    )
RETURN
    RIGHT ( "0" & INT ( datediff / 3600 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( ( datediff - INT ( datediff / 3600 ) * 3600 ) / 60 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( MOD ( MOD ( datediff, 3600 ), 60 ) ), 2 )

And finally you will get the following:

In my logic, as shown below (sorry for not being good in the drawing), blue is the start time and the end time of the table, red is the segmentation time, when the slicer is the next time, the time obtained by measure is green, otherwise the time side is blue. (You can modify the if decision according to your actual logic)

I hope it's useful to you!

Best regards

Lucien

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

It feels a bit confusing in terms of logic, you can combine my steps below and adapt them to your actual needs.

Step 1, create a date table for slicer:

dates = CALENDAR("2020/1/1","2021/12/21")

Step 2,create the following measure:

Date range =
VAR MinDate =
    CALCULATE ( MIN ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR min_date =
    MAX ( 'Table'[Start_date] )
VAR MaxDate =
    CALCULATE ( MAX ( dates[Date] ), ALLSELECTED ( dates[Date] ) )
VAR max_date =
    MAX ( 'Table'[end_date] )
VAR datediff =
    IF (
        MaxDate >= min_date
            && MaxDate <= max_date,
        DATEDIFF ( MaxDate, max_date, SECOND ),
        IF (
            MinDate >= min_date
                && MinDate <= max_date,
            DATEDIFF ( MinDate, max_date, SECOND ),
            DATEDIFF ( min_date, max_date, SECOND )
        )
    )
RETURN
    RIGHT ( "0" & INT ( datediff / 3600 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( ( datediff - INT ( datediff / 3600 ) * 3600 ) / 60 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( MOD ( MOD ( datediff, 3600 ), 60 ) ), 2 )

And  final you will get the below:

In my logic, as shown below (sorry for not being good at drawing), the blue is the start time and end time of the table, the red is the slicer time, when the slicer is the following time, the time obtained by measure is green, otherwise the time side is blue.（You can modify the if decision according to your actual logic)

Wish it is helpful for you!

Best Regards

Lucien

[rogerio_trabalh]

Hi, I have a question about this:

how do I calculate the total? 'cause it shows 01:10:00 but this is not the sum, right?

Thank you!

[MFelix]

Boa tarde @Anonymous ,

Os filtros estão estranhos por apresenta 15 de Março a 18 Janeiro, suponho que seja 15 Março a 18 Março.

O filtro que coloca é com base nas duas datas inicio e fim? Ou apenas numa delas?

Neste caso em que os valores estão em branco como é que determina qual a data que deverá ser utilizada será a data máxima (18/03/2021 23:59) ou a minima (15/03/2021 00:00)?

As primeira linha deveria dar 28:00 e não 28:30.

O cálculo passará por criar uma medida sendo que a sintaxe será semelhante a seguinte:

Hours = var timediff = SUMX('Table', DATEDIFF(MAX(MIN('calendar'[Date]), 'Table'[Inicio]), 'Table'[Fim], MINUTE)) 

return
INT(timediff)/60 + (timediff/60 - INT(timediff/60))*60

Mais uma vez frizo que não tenho a certeza que os dados apresentados estejam corretamentes calculados mas a formula a utilizar será similar a que tenho.