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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ozanboy
Frequent Visitor

DAX Calculate time value row by row based on condition from two different columns

Hi,

I am relatively new to power bi. I tried to solve via reading posts in here but could not come up with solution. 

So here it is, I want to calculate time values for each step in my experiments seperately. I have experiment ID, step number in this experiment, duration of that step. I want to calculate when this step started (for example first step always started at t=0) when it is ended and what was the timestamp (actual time) at that time.

Thanks in advance.

 

ID_Experiment_StepID_Experimentint_Step_notim_Step_Stop_Watchtim_Experiment_Start_TimeCalculate Step StartCalculate Step EndCalculate time stamp
451100:00:0114:18:2600:00:0000:00:0114:18:26
461200:02:5014:18:2600:00:0100:02:5114:18:27
471300:00:2214:18:2600:02:5100:03:1314:21:17
481400:07:1114:18:2600:03:1300:10:2414:21:39
491500:02:5914:18:2600:10:2400:13:2314:28:50
501600:01:4014:18:2600:13:2300:15:0314:31:49
511700:01:2014:18:2600:15:0300:16:2314:33:29
521800:00:0214:18:2600:16:2300:16:2514:34:49
531900:00:5314:18:2600:16:2500:17:1814:34:51
5411000:02:0214:18:2600:17:1800:19:2014:35:44
5511100:04:0514:18:2600:19:2000:23:2514:37:46
5611200:01:0614:18:2600:23:2500:24:3114:41:51
5711300:22:2314:18:2600:24:3100:46:5414:42:57
5811400:38:0314:18:2600:46:5401:24:5715:05:20
592100:00:2710:15:4700:00:0000:00:2710:15:47
602200:01:3810:15:4700:00:2700:02:0510:16:14
612300:01:4010:15:4700:02:0500:03:4510:17:52
622400:10:2410:15:4700:03:4500:14:0910:19:32
632500:03:0510:15:4700:14:0900:17:1410:29:56
642600:00:4310:15:4700:17:1400:17:5710:33:01
652700:01:2610:15:4700:17:5700:19:2310:33:44
663100:01:1812:09:5100:00:0000:01:1812:09:51
673200:00:0112:09:5100:01:1800:01:1912:11:09
683300:01:3512:09:5100:01:1900:02:5412:11:10
693400:02:0312:09:5100:02:5400:04:5712:12:45
703500:04:0212:09:5100:04:5700:08:5912:14:48
713600:01:1012:09:5100:08:5900:10:0912:18:50
723700:22:2312:09:5100:10:0900:32:3212:20:00
733800:37:3612:09:5100:32:3201:10:0812:42:23
743900:10:4612:09:5101:10:0801:20:5413:19:59
7531000:02:5912:09:5101:20:5401:23:5313:30:45

 

1 ACCEPTED SOLUTION
PattemManohar
Community Champion
Community Champion

@ozanboy  Please try below as New Columns. 

 

StepStart = 
VAR _StepStart = CALCULATE(SUM(Test284TimeLookup[StepStopWatch]),FILTER(Test284TimeLookup,Test284TimeLookup[ExpStepID]=EARLIER(Test284TimeLookup[ExpStepID]) && Test284TimeLookup[StepNo] < EARLIER(Test284TimeLookup[StepNo])))
RETURN IF(ISBLANK(_StepStart),"00:00:00",FORMAT(_StepStart,"HH:MM:ss"))
StepEnd = 
VAR _StepEnd = CALCULATE(SUM(Test284TimeLookup[StepStopWatch]),FILTER(Test284TimeLookup,Test284TimeLookup[ExpStepID]=EARLIER(Test284TimeLookup[ExpStepID]) && Test284TimeLookup[StepNo] <= EARLIER(Test284TimeLookup[StepNo])))
RETURN FORMAT(_StepEnd,"HH:MM:ss")
TimeStamp = FORMAT(Test284TimeLookup[ExperimentStartTime]+Test284TimeLookup[StepStart],"HH:MM:ss")

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

2 REPLIES 2
PattemManohar
Community Champion
Community Champion

@ozanboy  Please try below as New Columns. 

 

StepStart = 
VAR _StepStart = CALCULATE(SUM(Test284TimeLookup[StepStopWatch]),FILTER(Test284TimeLookup,Test284TimeLookup[ExpStepID]=EARLIER(Test284TimeLookup[ExpStepID]) && Test284TimeLookup[StepNo] < EARLIER(Test284TimeLookup[StepNo])))
RETURN IF(ISBLANK(_StepStart),"00:00:00",FORMAT(_StepStart,"HH:MM:ss"))
StepEnd = 
VAR _StepEnd = CALCULATE(SUM(Test284TimeLookup[StepStopWatch]),FILTER(Test284TimeLookup,Test284TimeLookup[ExpStepID]=EARLIER(Test284TimeLookup[ExpStepID]) && Test284TimeLookup[StepNo] <= EARLIER(Test284TimeLookup[StepNo])))
RETURN FORMAT(_StepEnd,"HH:MM:ss")
TimeStamp = FORMAT(Test284TimeLookup[ExperimentStartTime]+Test284TimeLookup[StepStart],"HH:MM:ss")

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Thanks @PattemManohar . 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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