Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Brighton10
Helper II
Helper II

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

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 belowalarms.PNGalarms1.PNG

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

@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 

 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
AlB
Community Champion
Community Champion

@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 

 

SU18_powerbi_badge

AlB
Community Champion
Community Champion

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 

 

SU18_powerbi_badge

 

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 alarmsreport.PNG

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.