If condition with time duration

[Zack007]

Hello,

Please help to have this duration negative calculation in the query, in Excel I used to use the function below.

The first is excluded from the sum of durations and automatically gets that -100 ( by default )to be identified as it was excluded

If I keep both lines as it is the sum of the two values 6:11:39 (Not correct) instead of 0 which is correct.

PS: It is a database extraction that I use in a daily manner. 

 Thank you 😊

[Zack007]

Hello,

As the forum's body does not support either .csv or .xlsx files, you could find below a part of the extraction as a table.

Correction

User

Name

Prd. order

Order descr. (PO)

Operation No.

Operation Descr.

Operation version

Machine No.

Machine descr.

From

Until

Capacity [%]

Duration [h]

Booking type

1

Z003R91E

El Harchaoui, Said

5.1E+09

GP475672

00-0070

1100-Demolding/Aux mat removal

1

LEWEB132

G132 Leading Edge Web

04-12-22 22:11

05-12-22 1:17

-100,00

3:05:49

Production time

0

Z003R91E

El Harchaoui, Said

5.1E+09

GP475672

00-0070

1100-Demolding/Aux mat removal

1

LEWEB132

G132 Leading Edge Web

04-12-22 22:11

05-12-22 1:17

3:05:50

0

Z003R91E

El Harchaoui, Said

5.1E+09

Meeting

00-0060

break

1

XW132_PS

G132 Prefabs Shells NP-NR

05-12-22 1:17

05-12-22 2:05

0:48:45

0

Z003R91E

El Harchaoui, Said

5.1E+09

GP475672

00-0070

1100-Demolding/Aux mat removal

1

LEWEB132

G132 Leading Edge Web

05-12-22 2:05

05-12-22 4:31

2:25:46

1

Z003R91E

El Harchaoui, Said

5.1E+09

GP475672

00-0070

1100-Demolding/Aux mat removal

1

LEWEB132

G132 Leading Edge Web

06-12-22 22:13

07-12-22 3:36

-100,00

5:22:52

Production time

0

Z003R91E

El Harchaoui, Said

5.1E+09

GP475672

00-0070

1100-Demolding/Aux mat removal

1

LEWEB132

G132 Leading Edge Web

06-12-22 22:13

07-12-22 3:36

5:22:53

0

Z003R91E

El Harchaoui, Said

5.1E+09

Meeting

00-0240

Waiting Due To Crane Movements

1

XW132_PS

G132 Prefabs Shells NP-NR

07-12-22 3:36

07-12-22 5:54

2:17:59

0

Z003R91E

El Harchaoui, Said

5.1E+09

Meeting

00-0010

Meeting

1

XW132_PS

G132 Prefabs Shells NP-NR

07-12-22 22:02

07-12-22 22:04

0:01:53

1

Z003RJZC

Ou Taleb, Mohamed

5.1E+09

GP475684

00-0030

700-Lay up

1

S_CAP132

G132 Suction Cap

05-12-22 7:43

05-12-22 11:43

100,00

4:00:05

Production time

1

Z003RJZC

Ou Taleb, Mohamed

5.1E+09

GP475684

00-0030

700-Lay up

1

S_CAP132

G132 Suction Cap

05-12-22 7:43

05-12-22 13:51

-100,00

6:08:06

Production time

0

Z003RJZC

Ou Taleb, Mohamed

5.1E+09

GP475684

00-0030

700-Lay up

1

S_CAP132

G132 Suction Cap

05-12-22 7:43

05-12-22 13:51

6:08:06

0

Z003RJZC

Ou Taleb, Mohamed

5.1E+09

Meeting

00-0010

Meeting

1

XW132_PS

G132 Prefabs Shells NP-NR

06-12-22 5:56

06-12-22 8:57

3:00:59

1

Z003RJZC

Ou Taleb, Mohamed

5.1E+09

GP475684

00-0020

600-In-line preparation

1

S_CAP132

G132 Suction Cap

06-12-22 8:57

06-12-22 13:41

-100,00

4:43:07

Production time

0

Z003RJZC

Ou Taleb, Mohamed

5.1E+09

GP475684

00-0020

600-In-line preparation

1

S_CAP132

G132 Suction Cap

06-12-22 8:57

06-12-22 13:41

4:43:07

[ryan_mayu]

which columns are the column M and column N?

[Zack007]

Please try to copy and paste this data into an excel sheet it will give the right separation  

[Zack007]

Hello,

Column N is [[ Duration [h] ]], Column M is [[ Capacity [%] ]].

Many Thanks

[ryan_mayu]

is this what you want?

Column = if('Table'[Name]=0,'Table'[Capacity [%]]]*'Table'[Duration [h]]]/100)*24

[Zack007]

Yes almost but not [Name] it's [Correction]

   ** Column = if('Table'[Name]=0,'Table'[Capacity [%]]]*'Table'[Duration [h]]]/100)*24 **

When I test it the app can not finde 'Table'  see screenshot below

Many thanks

[ryan_mayu · Accepted solution]

table is the table name, you can change it to your table name and have a try.

[Zack007]

Thank you very much, it's working😊😊

[Zack007]

Hello,

Any solutions please.

Thank you.

[ryan_mayu]

could you pls provide the sample data(not the screenshot)