Return Hours for Calendar Day

[rdraytonNBRS]

Hi - I have searched high and low for this answer, but I can't seem to make any of the solutions work.

I have the data in the table below, having used the Divide and DateDiff to give me an HoursPerDay column against each AssignmentID.

I want to then be able to use my Calendar [Date] to arrive at the hours per day on any given day for each AssignmentID or subsequently for any related field.

I have tried the below Measure, with no success

PlanDayHours = IF(

    MAX('Calendar'[Date])>=MAX(PNPlannedLabor[StartDate]) &&

    MAX('Calendar'[Date])<=MAX(PNPlannedLabor[EndDate]),

    MAX(PNPlannedLabor[HoursPerDay])

    ,0

)

Any assistance would be apprecaited.

[AlexanderPrime]

Recommend trying using the CROSSJOIN option via making a "New Table".

Provided you have a date table, select the "New Table" option outside of Power Query. (Not "Enter Data")

In the code field that appears, use the following code:

DateMatrix = 
FILTER(
CROSSJOIN(DATETABLE,ASSIGNMENTTABLE),
DATETABLE[DATECOLUMN] >= ASSIGNMENTTABLE[STARTDATE] && DATETABLE[CATECOLUMN] <=ASSIGNMENTTABLE[ENDDATE]

Replace DATETABLE with the name of the table for your dates, and DATECOLUMN with the name of the column of your dates.

Replace ASSIGNMENTTABLE with the name of your data with the assignment info in the first screenshot

You should then get a "new" merged table called DateMatrix, which you should be able to fill a Matrix Visual with the Assignment IDs in the Rows, Dates in the Columns, and HoursPerDay in Values  but make sure you are selecting these from the "DateMatrix" table and not the originals. 

[rdraytonNBRS]

That has created a table of 45,971,000 rows, which seems excessive, considering there were only 18,000 rows in my ASSIGNMENTTABLE.

[AlexanderPrime]

It's essentially calculating and assigning to an hour per day to an individual calendar date for each of your assignment IDs. 

For example, in your first row on that screenshot you provided, that will generate 7 rows, one for each date between 16th and 22nd of August. If you have a bunch of long term assignments in there then it's going to generate a lot. 

[rdraytonNBRS]

Yep - I've added

&&   'Calendar'[Date] >= (TODAY()-120) &&

    'Calendar'[Date] <= (TODAY()+120)  to at least limit back down to 75000 rows at this stage.  

I am now getting a circular dependancy error when I try to create a relationship to a higher level planning table so I'll need to solve for that as well.
Thanks