Forum Discussion

HxH's avatar
HxH
Icon for Advocate II rankAdvocate II
7 years ago
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 sta...
Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    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
Backlog

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
7 years ago

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
Backlog

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.