cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.

 ID_Experiment_Step ID_Experiment int_Step_no tim_Step_Stop_Watch tim_Experiment_Start_Time Calculate Step Start Calculate Step End Calculate time stamp 45 1 1 00:00:01 14:18:26 00:00:00 00:00:01 14:18:26 46 1 2 00:02:50 14:18:26 00:00:01 00:02:51 14:18:27 47 1 3 00:00:22 14:18:26 00:02:51 00:03:13 14:21:17 48 1 4 00:07:11 14:18:26 00:03:13 00:10:24 14:21:39 49 1 5 00:02:59 14:18:26 00:10:24 00:13:23 14:28:50 50 1 6 00:01:40 14:18:26 00:13:23 00:15:03 14:31:49 51 1 7 00:01:20 14:18:26 00:15:03 00:16:23 14:33:29 52 1 8 00:00:02 14:18:26 00:16:23 00:16:25 14:34:49 53 1 9 00:00:53 14:18:26 00:16:25 00:17:18 14:34:51 54 1 10 00:02:02 14:18:26 00:17:18 00:19:20 14:35:44 55 1 11 00:04:05 14:18:26 00:19:20 00:23:25 14:37:46 56 1 12 00:01:06 14:18:26 00:23:25 00:24:31 14:41:51 57 1 13 00:22:23 14:18:26 00:24:31 00:46:54 14:42:57 58 1 14 00:38:03 14:18:26 00:46:54 01:24:57 15:05:20 59 2 1 00:00:27 10:15:47 00:00:00 00:00:27 10:15:47 60 2 2 00:01:38 10:15:47 00:00:27 00:02:05 10:16:14 61 2 3 00:01:40 10:15:47 00:02:05 00:03:45 10:17:52 62 2 4 00:10:24 10:15:47 00:03:45 00:14:09 10:19:32 63 2 5 00:03:05 10:15:47 00:14:09 00:17:14 10:29:56 64 2 6 00:00:43 10:15:47 00:17:14 00:17:57 10:33:01 65 2 7 00:01:26 10:15:47 00:17:57 00:19:23 10:33:44 66 3 1 00:01:18 12:09:51 00:00:00 00:01:18 12:09:51 67 3 2 00:00:01 12:09:51 00:01:18 00:01:19 12:11:09 68 3 3 00:01:35 12:09:51 00:01:19 00:02:54 12:11:10 69 3 4 00:02:03 12:09:51 00:02:54 00:04:57 12:12:45 70 3 5 00:04:02 12:09:51 00:04:57 00:08:59 12:14:48 71 3 6 00:01:10 12:09:51 00:08:59 00:10:09 12:18:50 72 3 7 00:22:23 12:09:51 00:10:09 00:32:32 12:20:00 73 3 8 00:37:36 12:09:51 00:32:32 01:10:08 12:42:23 74 3 9 00:10:46 12:09:51 01:10:08 01:20:54 13:19:59 75 3 10 00:02:59 12:09:51 01:20:54 01:23:53 13:30:45

1 ACCEPTED SOLUTION
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")`

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

Proud to be a PBI Community Champion

2 REPLIES 2
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")`

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

Proud to be a PBI Community Champion

Frequent Visitor

Thanks @PattemManohar .

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors