Forum Discussion

matt0902's avatar
matt0902
Frequent Visitor
3 years ago

Reverse cumulative sum - to count dates

Hi there community

 

I am trying to figure out how to create a reverse cumulative sum that counts dates. I'd be grateful if anyone could help me with a Powerquery or DAX solution.

 

Input:

Required output in PowerBI (assuming today's date is 2/10/23):

 

 

Need Help

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    3 years ago

    Hey matt0902 ,

     

    please explain

    • from where the 28th of September is comin
    • why there is India with 3rd of October
    • why the the Forecast rev **bleep** is 2 for France at the 1st and 2nd of October.

    Regards,

    Tom

    • matt0902's avatar
      matt0902
      Frequent Visitor
      3 years ago

      Hi Tom

       

      The Forecast Date is when a defect is planned to be closed. The Actual Date is when the defect was closed. The reverse cumulative sum tracks the number of defects not yet closed over time (Forecast is the plan and Actual is the actual). I have included France and India to provide functionality to filter by a field - this could be a range of fields associated with the dataset e.g. country, location, responsible person, subsystem. Here are some example charts that I am trying to reproduce using the above data. This indicates that we are broadly on track to close everything as planned. Normally I would have a much bigger dataset so an extrapolation of the actual line into the future would be more reliable.

       

       

      Regards