cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Range of dates- calculating duration and sum of hours - from and to and sum of hours

Hi All, I am struggling for one of the requirements. please help me here.

I have data something like this whare I need to calculate number of days a resource took on a stretch and number of days too.

 

Resource ID          Leave transaction date                Hours
100101/03/20228
100102/03/20228
100103/03/20227
100104/06/20228
100105/06/20228
100106/06/20228
100107/06/20228
200203/02/20228
200204/02/20228
200205/02/20228
200206/02/20228
200203/05/20228
200207/06/20228
200208/06/20228
200209/06/20226

 

and I need something like this to be displayed in th ereport.

 

Resource ID      Leave start date                  leave end date                 Leave Hours          Leave days       
100101/03/202203/03/2022233
100104/06/202207/06/2022324
200203/02/202206/02/2022324
200203/05/202203/05/202281
200207/06/202209/06/2022223

 

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Anonymous 

maybe you can try to create two columns

start = minx(FILTER('Table','Table'[Resource ID]=EARLIER('Table'[Resource ID])&&year('Table'[Leave transaction date])=year(EARLIER('Table'[Leave transaction date]))&&month('Table'[Leave transaction date])=month(EARLIER('Table'[Leave transaction date]))),'Table'[Leave transaction date])

end = MAXX(FILTER('Table','Table'[Resource ID]=EARLIER('Table'[Resource ID])&&year('Table'[Leave transaction date])=year(EARLIER('Table'[Leave transaction date]))&&month('Table'[Leave transaction date])=month(EARLIER('Table'[Leave transaction date]))),'Table'[Leave transaction date])

then create a measure

days = DATEDIFF(max('Table'[start]),max('Table'[end]),DAY)+1

1.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
ryan_mayu
Super User
Super User

@Anonymous 

maybe you can try to create two columns

start = minx(FILTER('Table','Table'[Resource ID]=EARLIER('Table'[Resource ID])&&year('Table'[Leave transaction date])=year(EARLIER('Table'[Leave transaction date]))&&month('Table'[Leave transaction date])=month(EARLIER('Table'[Leave transaction date]))),'Table'[Leave transaction date])

end = MAXX(FILTER('Table','Table'[Resource ID]=EARLIER('Table'[Resource ID])&&year('Table'[Leave transaction date])=year(EARLIER('Table'[Leave transaction date]))&&month('Table'[Leave transaction date])=month(EARLIER('Table'[Leave transaction date]))),'Table'[Leave transaction date])

then create a measure

days = DATEDIFF(max('Table'[start]),max('Table'[end]),DAY)+1

1.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors