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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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