cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Proud to be a PBI Community Champion

Frequent Visitor

Thanks @PattemManohar .

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors