Why does my Dax Measure not caclulate all values when I add a date from the Date_Table?

[CJBoelt]

The task is to show two measures for Projects on a line graph, one being the actual hours worked each week, the other being a forecast of how many hours might be worked to fit the budget. I have a measure set that calulates this approximation.

Photo's will be the easiest to explain, I have a sample set of the model here. There are two fact tables, one being Project_Forecast and 

The Date_Table is so the values can share a common X-axis.

Here are some values for selected job 9555.

As you can see, the leftmost table for actual hours and the middle table for forecasted is correct, though when I add the Date column to the forecasted hours it cuts the dates to the actuals. Week_Seq# refers to what week of the project it is.


How can I join these two metrics together without truncating/shorting the forecasted hours? 

Here is my measure for the Approximate_Hours.

_approximateHours =

VAR Deviation = 5

VAR PeakWeek = 15 //when the job will have most hours worked

VAR MaxProductionListId = MAX(PROJECT_LOGS[Production_list_id]) // This finds the most recent Job_Log for updated budget

VAR MaxBudgetHours = LOOKUPVALUE(PROJECT_LOGS[Budget_Hours], PROJECT_LOGS[Production_list_id], MaxProductionListId) //finding the most recent Job Budget to apply to normal Distribution function

RETURN IF(MIN(PROJECT_FORECAST[Week_SEQ_Forecast]) <= 0, BLANK(),

    ROUNDUP(MaxBudgetHours * NORM.DIST(MIN(PROJECT_FORECAST[Week_SEQ_Forecast]), PeakWeek, Deviation, TRUE), 0)

    //Uses Normal Dist Function to fit to a model, rounds up,

)

Feel free to critique my data model if any changes should be made, or if you need the sample .pbix. Just trying to figure out why the forecast does not include the full range of weeks when the date_table is added.

Thank you!

[andhiii079845 · Accepted solution]

Hi @CJBoelt ,

he calculaction seems to swith per one row: 

6+11 = 17. Perhaps only a random event? First i would try to find out why 7/8/2022 is missing. Is this Date in the Date_table? Are you able to share the PBI without sensitve data via ondrive or dropbox?