cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

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 1001 01/03/2022 8 1001 02/03/2022 8 1001 03/03/2022 7 1001 04/06/2022 8 1001 05/06/2022 8 1001 06/06/2022 8 1001 07/06/2022 8 2002 03/02/2022 8 2002 04/02/2022 8 2002 05/02/2022 8 2002 06/02/2022 8 2002 03/05/2022 8 2002 07/06/2022 8 2002 08/06/2022 8 2002 09/06/2022 6

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

 Resource ID Leave start date leave end date Leave Hours Leave days 1001 01/03/2022 03/03/2022 23 3 1001 04/06/2022 07/06/2022 32 4 2002 03/02/2022 06/02/2022 32 4 2002 03/05/2022 03/05/2022 8 1 2002 07/06/2022 09/06/2022 22 3

1 ACCEPTED SOLUTION
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``

pls see the attachment below

Proud to be a 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``

pls see the attachment below

Proud to be a Super User!

Announcements

#### Power BI September 2023 Update

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

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### 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!

#### 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
Top Kudoed Authors