Forum Discussion

andrew_hardwick's avatar
andrew_hardwick
Helper 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.

     

andrew_hardwick's avatar
andrew_hardwick
Helper 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?

medwards807's avatar
medwards807
Helper I
9 years ago

andrew_hardwick

 

This solution works great, thanks alot for the help, I do have just one additional slight bug that I am trying to figure out as follows: I have 2 months where the Backlog is off by 1, is Oct 16 and Dec 16, it looks to be that what is different on these 2 months are the # Created is blank so the backlog seems to bot have recognized that until it hits the next day with a # created value. All of the other period's are correct.

 

My Measures:

 

# Created = CALCULATE(COUNTROWS(Incidents),USERELATIONSHIP(Incidents[Created Date],Dates[Date]),incidents[Status] <> "Cancelled")

 

# Resolved = CALCULATE(COUNTROWS(Incidents),USERELATIONSHIP(Incidents[Resolved Date],Dates[Date]),incidents[Status] <> "Cancelled")

 

Balance = CALCULATE(incidents[# Created] - incidents[# Resolved])

 

Backlog =
CALCULATE(incidents[Balance],FILTER(ALL('Dates'),and('Dates'[Date]<=max('Dates'[Date]), NOT(ISBLANK('Dates'[Date])))))