topic Re: Calculate running backlog based on two fact tables in DAX Commands and Tips

Calculate running backlog based on two fact tables

abaulo — Wed, 16 Feb 2022 20:22:45 GMT

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:

Have a Template date <= date context
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!

Re: Calculate running backlog based on two fact tables

Greg_Deckler — Thu, 17 Feb 2022 18:43:06 GMT

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.

Re: Calculate running backlog based on two fact tables

abaulo — Mon, 21 Feb 2022 20:43:36 GMT

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) )