cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors