Calculate running backlog based on two fact tables

[abaulo]

Hello everyone!
I'm still getting my bearings with DAX and Data Modeling, so I'm unsure how to phrase the question.
I've tried wording it many ways to find an existing solution to no avail. Even if you're input is a link to the appropriate forum post, it would be very much appreciated.

I've stripped down the tables for sharing the basic structure of the data.

Here is a link to the file: Sample file

The Template table records trips to the job site to measure/draw the fabrication template. 

The Install table records installation trips to the job site. 

I'm trying to find a way to sum the square footage from the Template table for Jobs that: 

1. Have a Template date <= date context
2. Do not have an Install date <= date context

I'm calling this the "Backlog" of footage that needs to be installed.

Backlog =
VAR MaxDate =
    //Get the latest date from the Week of Year context
    MAX ( 'Calendar'[Date] )
VAR TemplatedJobs =
    //Get JobIDs of Templated Jobs thus far
    CALCULATETABLE (
        SUMMARIZE (
            Template,
            Job_Info[JobID]
        ),
        Template[Template Date] <= MaxDate
    )
VAR InstalledJobs =
    //Get JobIDs of Installed Jobs thus far
    CALCULATETABLE (
        SUMMARIZE (
            Install,
            Job_Info[JobID]
        ),
        Install[Install Date] <= MaxDate
    )
VAR TemplatedNotInstalled =
    //Get JobIDs of jobs that have been Templated but not Installed
    EXCEPT (
        TemplatedJobs,
        InstalledJobs
    )
RETURN
    //Sum the Sqft of those jobs
    CALCULATE (
        [Templated Footage], //= SUM(Template[SqFt])
        TemplatedNotInstalled
    )

I'm expecting to see the Backlog footage change from week to week as installs happen, but so far it is just matching the Templated Footage measure:

Though the "Total" seems to be correct...so that's a start.

How can I get the individual weeks to show the correct Backlog?

Thanks in advance for any assistance you can provide!

[abaulo · Accepted solution]

@Greg_Deckler , thanks for the tips!

I used CONCATENATEX to figure out what was happening in each CALCULATETABLE. I eventually got the following to work, though I'm still not entirely sure why I have to include ALL in each CALCULATETABLE, which leads me to believe there's probably a more elegant solution.

Backlog = 

var MaxDate = //Get the latest date from the Week of Year context
    MAX('Calendar'[Date]) 

var TemplatedJobs = //Get JobIDs of Templated Jobs thus far
    CALCULATETABLE(
        SUMMARIZE(
            Template,
            Job_Info[JobID]
        ),
        Template[Template Date] <= MaxDate, 
        ALL(Template)
    )

var InstalledJobs = //Get JobIDs of Installed Jobs thus far
    CALCULATETABLE(
        SUMMARIZE(
            Install,
            Job_Info[JobID]
        ),
        Install[Install Date] <= MaxDate,
        ALL(Install)
    )

var Backlog = //Get JobIDs of jobs that have been Templated but not Installed
    EXCEPT(
        TemplatedJobs,
        InstalledJobs
    )

Return  //Sum the Sqft of those jobs
    CALCULATE(
        [Templated Footage], //= SUM(Template[SqFt])
        Template[JobID] IN Backlog,
        ALL(Template)
    )

[Greg_Deckler]

@abaulo I don't see anything obviously wrong with the calculation. I will have to pull down the file and take a closer look. What you can do is to change your RETURN statement to make sure the individual parts of the formula are correct, like returning MaxDate. You can also use CONCATENATEX against your table variables to "look inside" those tables and see what they contain and if they contain the correct things.