Difference between rows based day filter

[Flashback87]

Hello,

i have this data in source (Odpoledne = Afternoon, Dopoledne=morning,Konec=end)

and i need calculate total run time per day (based RecordDate)

Something like this (Morning/End-Morning/Start) + (Afternoon/End - Afternoon/Start) 

Is it possible?

Thanks a lot

Jakub

[Anonymous · Accepted solution]

Hi @Flashback87 

Change the data type of 'StatisticValueText'column to time, then  create a calculated column.

TotalMintes =
VAR _filter =
    FILTER (
        'Table',
        [IDOfRecord] = EARLIER ( 'Table'[IDOfRecord] )
            && [InstrumentCode] = EARLIER ( 'Table'[InstrumentCode] )
            && [RecordDate] = EARLIER ( 'Table'[RecordDate] )
    )
VAR _morning =
    FILTER ( _filter, [StatisticGroupName] = "Dopoledne" )
VAR _after =
    FILTER ( _filter, [StatisticGroupName] = "Odpoledne" )
RETURN
    DATEDIFF (
        CONVERT (
            [RecordDate] & " "
                & MINX ( _morning, [StatisticValueText] ),
            DATETIME
        ),
        CONVERT (
            [RecordDate] & " "
                & MAXX ( _morning, [StatisticValueText] ),
            DATETIME
        ),
        MINUTE
    )
        + DATEDIFF (
            CONVERT ( [RecordDate] & " " & MINX ( _after, [StatisticValueText] ), DATETIME ),
            CONVERT ( [RecordDate] & " " & MAXX ( _after, [StatisticValueText] ), DATETIME ),
            MINUTE
        )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.