Calculate duration between undefined amount of dates

[TeoTreny]

Hi,

I need to calculate the total amount of duration between an unknow amount of start/end dates. I also need to make sure not to include a duration if the current time is before the start time, or to include the duration between start and current if it has started but not ended.

 Here is a sample of my data :

Area (text)	Plot (number)	StartDate (datetime)	EndDate (datetime)
A	1	21/12/2023 05:40:10	21/12/2023 06:40:06
A	2	21/12/2023 08:06:13	21/12/2023 08:48:32
A	3	21/12/2023 9:20:00	21/12/2023 10:00:00
A	1	21/12/2023 9:20:00	21/12/2023 10:00:00
B	1	21/12/2023 6:33:14	21/12/2023 6:50:56

Moreover, I would like to group the result by Plot-Area so that I only have one row for each Plot-Area. I specify that my table is in DirectQuery mode, which limits access to a number of functions.

Please forgive my poor choice of words, english is not my mother tongue.

Téo T.

[Anonymous]

Hi @TeoTreny ,

You can create a calculated column as below to get it:

Column = 
VAR _Seconds =
    DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], SECOND )
VAR _Minutes =
    INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds =
    MOD ( _Seconds, 60 )
VAR _Hours =
    INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes =
    MOD ( _Minutes, 60 )
RETURN
    FORMAT ( _Hours, "00" ) & ":"
        & FORMAT ( _RemainingMinutes, "00" ) & ":"
        & FORMAT ( _RemainingSeconds, "00" )

Best Regards

[TeoTreny]

Thank you for your answer.
It works perfectly but doesn't consider the current time, it just calculates the duration between the dates. I need to compare the start date to current time to check if the break has started, check if the current time is lower than end date to know if the break is finished, and calculate the correct duration considering the situation. I know how to do it in DAX but I was wondering if it was possible with DirectQuery considering that my table is in Direct Query mode.

Later I need to group by Area - Plot in order to have only one row for each Area - Plot.

Is it possible ? If not I will accept your post as a solution.

[Anonymous]

Hi @TeoTreny ,

Could you please explain and provide the expected result with some sample data for the following sentences? It would be helpful to find the solution. Thank you.

1. Compare the start date to current time to check if the break has started, check if the current time is lower than end date to know if the break is finished, and calculate the correct duration considering the situation.  

2. Later I need to group by Area - Plot in order to have only one row for each Area - Plot.

---

Best Regards

[TeoTreny]

Hi, I would like to answer your request but my post keeps getting deleted without telling me why...
Is it because it includes 3 tables ?

EDIT : Here is a picture of the example to show you my desired output. (I would like to include table so you can copy but my post keeps getting deleted).

For the first step, I have to consider the actual time (as a datetime, let's name it ActualDate).
The logic looks like this :
IF ActualDate < StartDate : 0 minute have passed
IF StartDate < ActualDate < EndDate : [ActualDate - StartDate] minutes have passed
IF EndDate < ActualDate : [EndDate - StartDate] minutes have passed.

For the second step, I just want to have one row for each Area - Plot. It is much easier for me to deal with later on.

Please note that the input table is in DirectQuery mode.

[Anonymous · Accepted solution]

Hi @TeoTreny ,

You can create a measure as below to get it, please find the details in the attachment.

Note: My current date time is on 28/12/2023 11:15:00~

TotalDuartion = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[Area],
        'Table'[Plot],
        'Table'[StartDate],
        'Table'[EndDate],
        "@duration",
            SWITCH (
                TRUE (),
                NOW () < 'Table'[StartDate], 0,
                'Table'[StartDate] < NOW ()
                    && 'Table'[EndDate] > NOW (), DATEDIFF ( 'Table'[StartDate], NOW (), MINUTE ),
                'Table'[EndDate] < NOW (), DATEDIFF ( 'Table'[StartDate], 'Table'[EndDate], MINUTE )
            )
    )
RETURN
    SUMX ( _tab, [@duration] )

Best Regards

[TeoTreny]

Thanks a lot !
I had to modify it a little but it does the job perfectly !

[VijayP]

@TeoTreny 

Hopefully it should work

[TeoTreny]

Hi,
Thanks for your quick answer. Indeed, it works but only in Import mode, DirectQuery doesn't allow this :

Translated in english it means : "This step generates a query that is not supported in DirectQuery mode --> Switch table to Import mode" 

This is mostly my biggest problem, DirectQuery is making this really hard.

[TeoTreny]

Forgot to include the error, my bad :

Translated : "Sorry... Couldn't use the < operator with Date and DateTime"