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

[Anonymous]

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

[ryan_mayu · Accepted solution]

@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