Sum timestamps based on a condition

Question

I have a table like this:

Timings	Value
20.12.2022 00:00:00	0
20.12.2022 00:00:15	0
20.12.2022 00:00:45	1
20.12.2022 00:05:00	1
20.12.2022 00:05:15	1
20.12.2022 00:05:45	0
20.12.2022 00:06:00	0
20.12.2022 00:06:15	1
21.12.2022 00:06:40	1
21.12.2022 00:07:00	0
21.12.2022 01:07:00	0

I need to calculate the amount of time when the value is equal to 1.

In the above table, it will result in (20.12.2022 00:00:45 to 20.12.2022 00:05:15) + (20.12.2022 00:06:15 to 21.12.2022 00:06:40) = 4min 30sec + 24hr 0min 25sec = 24hr 4 min 55 sec (24:04:55).The measure should not overflow if the calculated value is above 24 hours.

I tried to calculate the value using the following DAX measure:

Measure = CALCULATE(SUM(Tabelle1[Timings]),FILTER(Tabelle1,Tabelle1[Value] = 1)) - CALCULATE(SUM(Tabelle1[Timings]),FILTER(Tabelle1,Tabelle1[Value] = 0))

Sum of the timings when the value is 1 - Sum of the timings when the value is 0.This measure is giving me an incorrect answer.

How to write a DAX expression to calculate this?

Answer 1

Add this calculated column to your table:

Delta1 = 
VAR _this_timings = 
    Tabelle1[Timings]
VAR _this_value = 
    Tabelle1[Value]
VAR _prev_timings =
    CALCULATE(
        MAX(Tabelle1[Timings]),
        FILTER(
            Tabelle1,
            Tabelle1[Timings] < _this_timings
        )
    )
VAR _prev_value = 
    CALCULATE(
        MAX(Tabelle1[Value]),
        FILTER(
            Tabelle1,
            Tabelle1[Timings] = _prev_timings
        )
    )
VAR _delta =
    DATEDIFF(_prev_timings, _this_timings, SECOND)
RETURN
    IF(_prev_value = 1 && _this_value = 1, _delta, 0)

and then use this measure to get your result:

Sum Delta1 = 
VAR _seconds = SUM(Tabelle1[Delta1])
VAR _minutes = INT(_seconds/60)
VAR _remaining_seconds = MOD(_seconds, 60)
VAR _hours = INT(_minutes/60)
VAR _remaining_minutes = MOD(_minutes, 60)
RETURN
    _hours & ":" 
    & FORMAT(_remaining_minutes, "0#") 
    & ":" & FORMAT(_remaining_seconds, "0#")

Answer 2

Difference in Seconds =

VAR Durations=
    ADDCOLUMNS(
           tbl
           ,"@Duration"
                ,VAR currentT=[Timings]
                VAR currentV=[Value]
                VAR prevT=
                    CALCULATE(
                        MAX([Timings])
                        ,'tbl'[Timings]<currentT
                        ,All()
                    )
                VAR prevV = 
                        CALCULATE(
                            SELECTEDVALUE(tbl[Value])
                            ,'tbl'[Timings]=prevT
                            ,All()
                        )

                RETURN 
                    SWITCH(
                        TRUE()
                        ,currentV=prevV,DATEDIFF(prevT,currentT,SECOND)
                        ,0
                    )
    )

VAR Sum1 =
        SUMX(
            FILTER(
                Durations
                ,[Value]=1
            )
            ,[@Duration]
        )
VAR Sum0 =
        SUMX(
            FILTER(
                Durations
                ,[Value]=0
            )
            ,[@Duration]
        )
RETURN
    Sum1-Sum0