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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors