Forum Discussion
Solved
Cumulative backlog calculation
Hi everyone,
I'm trying to create a measure that computes the cumulative difference of OPENED PRODUCTS vs CLOSED PRODUCTS and then split it by month to see how the team is doing. I am using the standard cumulative total pattern to perform such operations but it won't work. This is the measure:
Backlog =
var ClosedContracts = CALCULATE(COUNT('FactTable'[Close Date]),USERELATIONSHIP('Date'[Date],'FactTable'[Close Date]))
var ProductsDelivered = CALCULATE(COUNT('FactTable'[Delivery Date]),USERELATIONSHIP('Date'[Date],'Salesforce Data'[Delivery Date]))
var Differential = ClosedContracts - ProductsDelivered
var Cumulative = FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date]))
var Backlog = CALCULATE(
Differential,
Cumulative)
return
Backlog
The measure should computer this, to be clear:
-In January I close 5 contracts and deliver 3 products. Differential is 2 and backlog is 2.
-In February I close 5 contracts and deliver 0 produtcs. Differential is 5 and backlog is 7, as it should sum the backlog of the past month.
And so on.
The problem is that what it is computing instead is the difference between closed contracts and delivered products IN EVERY MONTH. So, in the previous example, backlog of february is 5 instead of 7 because it doesn't add the 2 from january.
Could the problem be related to relationship between tables or something? Because the pattern in DAX is pretty common and I've used many times. I don't see what I'm missing.
Thanks for any help
The measure should computer this, to be clear:
-In January I close 5 contracts and deliver 3 products. Differential is 2 and backlog is 2.
-In February I close 5 contracts and deliver 0 produtcs. Differential is 5 and backlog is 7, as it should sum the backlog of the past month.
And so on.
The problem is that what it is computing instead is the difference between closed contracts and delivered products IN EVERY MONTH. So, in the previous example, backlog of february is 5 instead of 7 because it doesn't add the 2 from january.
Could the problem be related to relationship between tables or something? Because the pattern in DAX is pretty common and I've used many times. I don't see what I'm missing.
Thanks for any help
Hi HxH ,
You may add SUMX function in your formula like DAX below.
Backlog = var ClosedContracts = CALCULATE(COUNT('FactTable'[Close Date]),USERELATIONSHIP('Date'[Date],'FactTable'[Close Date])) var ProductsDelivered = CALCULATE(COUNT('FactTable'[Delivery Date]),USERELATIONSHIP('Date'[Date],'Salesforce Data'[Delivery Date])) var Differential = ClosedContracts - ProductsDelivered var Cumulative = FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])) var Backlog = CALCULATE( SUMX('Date', Differential), Cumulative) return BacklogBest Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi HxH ,
You may add SUMX function in your formula like DAX below.
Backlog = var ClosedContracts = CALCULATE(COUNT('FactTable'[Close Date]),USERELATIONSHIP('Date'[Date],'FactTable'[Close Date])) var ProductsDelivered = CALCULATE(COUNT('FactTable'[Delivery Date]),USERELATIONSHIP('Date'[Date],'Salesforce Data'[Delivery Date])) var Differential = ClosedContracts - ProductsDelivered var Cumulative = FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])) var Backlog = CALCULATE( SUMX('Date', Differential), Cumulative) return BacklogBest Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.