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

## get MIN, MAX DATE then count duration in hour

hi all;

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)

Azura

1 ACCEPTED SOLUTION
Resolver II

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 :

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)

4 REPLIES 4
Resolver II

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)

Frequent Visitor

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

Resolver II

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 :

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Resolver II

Hi @bikgx ,

I am using new column

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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 - August 2024

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

Top Solution Authors
Top Kudoed Authors