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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

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

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors