Forum Discussion

HxH's avatar
HxH
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Problem in cumulative backlog measure

I everyone, I'm currently having problems calculating the backlog of our sales team. The backlog for a given month is defined as the difference between closed contracts and products delivered to customers, plus the cumulative differences of past months. To put it simply with an example:

January = I close 10 contracts and deliver 5 products. I have a backlog (products that I still have to deliver) of 5
February = I close 10 contracts and deliver 12 products. Now I have a backlog of 3 (-2+5 of the past month) 
March = I close 10 contracts and deliver 0 products. Now I have a backlog of 13 (the 3 products from past month plus 10 new) 
And so on 

This is the measure I am using: 

Backlog =
var ClosedContracts = CALCULATE(COUNT('Table'[Close Contract Date]),USERELATIONSHIP('Date'[Date],'Table'[Close Contract Date]))
 
var ProductsDelivered = CALCULATE(COUNT('Table'[Delivery Date]),USERELATIONSHIP('Date'[Date],'Table'[Delivery Date]))
 
var Differential = ClosedContracts - ProductsDelivered
 
var Backlog = CALCULATE( Differential, FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])))
 
return
Backlog

I then put month column from the Date table on the X axis. The problem is that this measure is calculating the differential in each month without cumulating the differences of past months. In the first month the backlog is correct (I close 10 and deliver 5, I have a backlog of five), but in the second month and after it is wrong (if I close 10 and deliver 5, I still have a backlog of 5 because it's just calculating the difference in the month without summing the backlog of january, which is also 5)

Any help? 


Need Help
  • HxH's avatar
    HxH
    6 years ago

    Hi, I'm sorry but it didn't work. 

    The solution I've found is to put the filter on the Date (the filter all <max part of the formula) inside each measure where I count the new contracts and completed deliveries, and then simply to the differential between them. It works this way

3 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi HxH ,

     

    Based on the example you shared. We can create a measure use following formula to meet your requirement.

     

    BackLog =
CALCULATE (
    SUM ( 'Table'[ClosedContracts] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Close Contract Date] <= SELECTEDVALUE ( 'Date'[Date] )
    )
)
    - CALCULATE (
        SUM ( 'Table'[ProductsDelivered] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Delivery Date] <= SELECTEDVALUE ( 'Date'[Date] )
        )
    )

     

    If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi HxH ,

     

    How about the result after you follow the suggestions mentioned in my original post?

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • HxH's avatar
      HxH
      Icon for Advocate II rankAdvocate II
      6 years ago

      Hi, I'm sorry but it didn't work. 

      The solution I've found is to put the filter on the Date (the filter all <max part of the formula) inside each measure where I count the new contracts and completed deliveries, and then simply to the differential between them. It works this way