Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bikgx
Frequent Visitor

get MIN, MAX DATE then count duration in hour

hi all;

 

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

 

image pbi.PNG

 

 

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

1 ACCEPTED SOLUTION

Hi @bikgx ,

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

shreyamukkawar_0-1669888540980.png

 

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 :
shreyamukkawar_1-1669888577356.png

 

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!

View solution in original post

4 REPLIES 4
shreyamukkawar
Resolver II
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)
 
shreyamukkawar_0-1669875147531.png

 

Best Regards,
Shreya Mukkawar

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

 

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

Hi @bikgx ,

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

shreyamukkawar_0-1669888540980.png

 

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 :
shreyamukkawar_1-1669888577356.png

 

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!

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors