get MIN, MAX DATE then count duration in hour

[bikgx]

hi all;

i have data set, https://docs.google.com/spreadsheets/d/159xOcmrKj95YXYOayHmGi9A_K_8ZHIMq/edit?usp=sharing&ouid=10007... 

the point is; i already get MIN DATE, and MAX DATE ( with Measurement); but then how to get duration(hour) from that ? i tried using DATEDIFF formula but the result is wrong .

First Started =

CALCULATE (
MIN ( 'Data'[DateStarted] ),
ALLEXCEPT ( 'Data', Data[DateCreate], Data[worker] )
)

Last Finished =

CALCULATE (
MAX ( 'Data'[DateFinished ),
ALLEXCEPT ( 'Data', Data[DateCreate], Data[worker] )
)

duration(hour)= datediff([Last Finished] , [First Started],HOUR)

thank you for your help;

Azura

[shreyamukkawar]

Hi @bikgx ,

1.

First Started = MIN(Sheet1[DateStarted])

2. 

Last Finished = MAX(Sheet1[DateFinished])

3.

Duration(Hours) = DATEDIFF(Sheet1[First Started],Sheet1[Last Finished],HOUR)

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[bikgx]

hi @Shhreya;

are you using new measurement or new column ?

i tried to create new column but the result is not what i expected; ineed all column i expected not just firt,last and duration and i have hundred rows raw data;

best regards

[shreyamukkawar · Accepted solution]

Hi @bikgx ,

I have added some workers in the data to cross-check.

1. Create new table as

Table = SUMMARIZE(Sheet1,Sheet1[worker],"firstdate",MIN(Sheet1[DateStarted]),"lastdate",MAX(Sheet1[DateFinished]))

2. Create new column as 

hours = DATEDIFF('Table'[firstdate],'Table'[lastdate],HOUR)

Output :

Hope I have answered your question.

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[shreyamukkawar]

Hi @bikgx ,

I am using new column

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!