create a start and end time calculated from the datetime based on a column condition

[Brighton10]

Greetings everyone. I am new to DAX and need assistance. I have a table with a datetime column. I want to create a start time whenever the alarmStatus is on and and end time when its off. I then want to calculate the difference between these 2 new columns as shown below

[AlB · Accepted solution]

@Brighton10 

1. Remove dateTime and alarmStatus from the visual. 

2. Perhaps you need to update the measures:

Start Time = CALCULATE( MIN( Table1[dateTime] ), Table1[alarmStatus] = "ON")

End Time = CALCULATE( MAX( Table1[dateTime] ), Table1[alarmStatus] = "OFF")

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Brighton10 

1. Place the relevant fields in a table visual

2. Place these measures in the visual

Start Time = MIN( Table1[dateTime] )

End Time = MAX( Table1[dateTime] )

Duration (mins) = ( [End Time] - [Start Time] )* 24*60

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Brighton10]

Hi @AlB 

I have implemented what you suggested but its not doing the calculation. I have filtered the country, region, sitename and its show zero (0) duration. So the function needs to check when filtered if the alarmStatus is on based on the previous time it was off then create a start time from there and do the same for when off. For example TerrainLightsOn was ON on 2021/01/24 which is supposed to be my start time then the next consecutive time its off is my end time. Thanks