Forum Discussion

andrew_hardwick's avatar
andrew_hardwick
Icon for Helper III rankHelper III
10 years ago
Solved

Cumulative backlog to date

I am trying to create a line graph in Power BI to show the difference between a number of opened tickets vs resolved tickets on a daily basis. I then want to show a line graph of the cumulative data....
Need Help
  • Sean's avatar
    Sean
    10 years ago

    andrew_hardwick Couple small changes since there are blank dates...  But you should be set now! Let me know.

     

medwards807's avatar
medwards807
Icon for Helper I rankHelper I
9 years ago

I am working on Something very similar and following the details of this post. I am reviewing the details and can't seem to get the measures listed to work.

 

My sample data is as follows:

 

I have open tickets and resolved tickets working just fine on my Bar Chart, just can't get the Backlog Line to work. 

The logic is I have to work backwards as follows:

 

I start with the current Active Cases (which is the 241 July-17 Backlog, then I add in the Resolved and subtract out the new. i.e.

 

June 17 Backog = 241 + 19 - 26

 

I have the Dates Tabe and # Created and # Resolved Measures working, could you share detail on how you calulated your Backlog ?

 

 OpenedResolvedBacklog
Apr-173424212
May-173836214
Jun-174020234
Jul-172619241

 

This is the end result based on the sample data.

 

andrew_hardwick's avatar
andrew_hardwick
Icon for Helper III rankHelper III
9 years ago

Hi medwards807

 

OK I have opened and resolved, which are both measures.

 

Then I have two calculations. The first is a measure called balance.

 

Balance = [Opened]-[Resolved]

 

I then use another measure called RT Balance (Running Total). This is the one that I use on my graph.

 

RT Balance = CALCULATE([Balance],FILTER(ALL('Date'),'Date'[Date]<=max('Date'[Date])&&NOT(ISBLANK('Date'[Date]))))

 

This looks like this-

 

 

Hope that helps?