Calculated column that references another table column if current value is blank

[AlixB]

I have a column in a table where some values are blank and need to be populated with a value from another table. 

the Hours YTD value is what is needed to populate the blanks

It needs to be done as a calculated column/measure as needs to be dynamic, the replacement value is based on the date filter.

There is a relationship between both tables which are date columns. However the blank fields have blank date fields too. There is a blank option in the date slicer which allows for blanks to show in the table.

I've tried the below abut it doesn't work as there is nothing to link the tables so returns nothing.

Hours Available =

IF(CALCULATE(SUM('Report Reliability_YearlyTotal'[TimeAvailable])) = BLANK(),

AVERAGE('Date'[DimDateMonthHours]),

CALCULATE(SUM('Report Reliability_YearlyTotal'[TimeAvailable])))

Any help would be appreciated

[lbendlin]

if you need it dynamic then it cannot be a calculated column - it has to be a measure.

How are your rows sorted, or does that not matter?

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good, or provide a sample pbix) .

[AlixB · Accepted solution]

@lbendlin 

I actually solved the issue in SQL so that my source data has the information for the last 5 years, but thanks for looking at it