How to Calculate "Backlog" on each date in DAX?

[Dave1mo1]

Hi, all.

I'm trying to calculate my company's historic backlog over the last several years, then plot it on an area chart. I have the following relevant fields:

• Booked Date for the Order
• Request Date for the Order
• Invoice Date for the Order
• Units
• Sales

I also have a primary relationship set up between Invoice Date & my Date Table, and a secondary relationship set up between Booked Date and my Date Table.

My first need is to know, on any given day, the number of units that had been booked but not yet invoiced. That's the most basic definition of backlog. So calculate [Total Units] on that date if the order has been booked on or before that day, but not yet invoiced. I just don't know how to get the syntax right in my DAX.

In plain English - Backlog Units equals, for any specific date, the number units associated with orders that have been Booked by that specific date, but not yet Invoiced by that date. How do I do that?

Bonus Question: I'd also like to be able to bucket the backlog by the time between "Booked Date" and "Request Date" (when the customer wants the unit delivered), then display those on an area chart as well, so we can those buckets vary over time. How do I create that calculation?

Thanks so much!

[Dave1mo1]

Hi,

So sorry for the delayed response. I'm looking to be able to plot this on an area or line chart over time, which I'm not able to do with the measure above. How would I adjust the measure to be able to calculate what the backlog was on each date and produce something like the chart below?

[v-zhouwen-msft · Accepted solution]

Hi @Dave1mo1 ,

The Table data is shown below:

Use the following DAX expression to create a measure

Measure = 
VAR _a = MAX('Date'[Date])
VAR _b = SUMX(FILTER('Table','Table'[Booked Date for the Order] <= _a && ISBLANK('Table'[Invoice Date for the Order]) || 'Table'[Invoice Date for the Order] > _a ),[Units])
RETURN _b

Final output