Getting duration of a cleaning procedure - DAX

[Ana_Cardoso]

Hey Guys me and @mayara_tavares

have a dashboard to monitoring cleaning procedure andwe execute this procedure Serveral times during the day. So we Have to calculate the duration in each of the procedures. Here an example of the data.

When the value is 14 the procedure is runing and when the value goes to 30 or 31 the procedure are done.

Time stamping		Value
27/06/2020 05:40		0,00
27/06/2020 05:40		0,00
27/06/2020 05:41		14,00
27/06/2020 05:43		14,00
27/06/2020 05:43		14,00
27/06/2020 05:43		14,00
27/06/2020 05:45		14,00
27/06/2020 06:14		14,00
27/06/2020 06:17		14,00
27/06/2020 06:17		14,00
27/06/2020 06:17		30,00
27/06/2020 06:17		0,00
27/06/2020 06:21		12,00
27/06/2020 06:24		12,00
27/06/2020 06:24		12,00
27/06/2020 06:24		12,00
27/06/2020 06:25		14,00
27/06/2020 06:26		14,00
27/06/2020 06:26		14,00
27/06/2020 06:26		14,00
27/06/2020 06:53		14,00
27/06/2020 06:55		14,00
27/06/2020 06:55		14,00
27/06/2020 06:57		31,00
27/06/2020 06:57		0,00

So, in this case we should have two procedures and the durantion of each one of them.

number of occurency	Duration (minutes)
1	36
2	32

Could someone help us with a DAX measure to calculate this duration?

Just for note: we are using direct query I can only use DAX for it

[MFelix]

Hi @Ana_Cardoso  and @mayara_tavares ,

You can create the following measure:

Duration =
VAR StartTime =
    MINX ( FILTER ( 'Table', 'Table'[Value] = 14 ), 'Table'[Time stamping] )
VAR EndTime =
    MAXX (
        FILTER ( 'Table', 'Table'[Value] IN { 30; 31 } ),
        'Table'[Time stamping]
    )
RETURN
    DATEDIFF ( StartTime, EndTime, MINUTE )

I'm assuming you have an ID column for the ocurrency number if not you need to have one, tell me if you need any assistance on making it.

Check PBIX file attach.

[mayara_tavares]

hi @MFelix Thank for you help! We don't have an ID column.

How We can create this in a DAX? because we can create a new column because our data is directquery

Thank you!

[MFelix]

Hi @mayara_tavares ,

Do you have any other fields that you use on your table to know that is changing from one job to the other or is it just when the 0 value appears on the table?

On your example you start at 5:40AM and the go until 6:17AM and a new one starts is there any column or columns that you reference as if it was an ID column?