Matrix Table with hours done per day with Start/End Date

[AlexanderPrime]

Hi all.

Got a bit of a different query about "Start/End Dates" with time spent on each day needing to appear on a Matrix Table showing which days has a team member working for on a project.

Unfortunately the data provided is a bit basic and is in the following format. (Table name for example is: RESOURCE)

I also have a Date Table (DateTable) set up that will show the Day Number of the Month and Day Name. (Relationship is currently set to look at Start Date as Active with the Date Table Index, and the index also looks at End Date as an inactive relationship)

ID (Index)	Staff Name      .	Start Date           .	End Date               .	Hours/Day
1	Staff 1	12/04/2022	12/04/2022	1.5
2	Staff 2	12/04/2022	12/04/2022	0.5
3	Staff 1	13/04/2022	16/04/2022	2
4	Staff 1	13/04/2022	13/04/2022	1
5	Staff 3	13/04/2022	13/04/2022	2

I'm trying to get it to output it to a matrix like below.

Staff Name	12/04/2022     .	13/04/2022        .	14/04/2022        .	15/04/2022       .	16/04/2022       .	17/04/2022      .
Staff 1	1.5	3	2	2	2	0
Staff 2	0.5	0	0	0	0	0
Staff 3	0	2	0	0	0	0

Essentially I need it to count everything on that day, including items over a set course of days.

Currently if I set up the matrix to show:

Rows: Staff Name
Columns: Day No (This is from the DateTable)
Values: Hour/Day

It will show most of this correctly. But Staff 1's time will not show the added hours from 14th to 16th, because EndDate isn't currently being factored in.

I'm thinking I need to do a calculation to assign these values on the DateTable potentially? But not quite sure which approach to take, or if there is a simpler way to do so. Any suggestions?

[AlexanderPrime · Accepted solution]

Figured it out via browsing other topics. Posting solution for others (We all hate those people who post "solved it" and never say HOW don't we!)

Needed to create a table but add the following code to crossjoin DateTable and Resource.

DateMatrix =

FILTER(

CROSSJOIN('DateTable','Resource'),

'DateTable'[Date] >= Resource[Start Date] && 'DateTable'[Date] <=Resource[End Date]

)

Then use this DateMatrix table as values on the matrix visual.