cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Re: Cumulative backlog to date

Hi @andrew_hardwick, I have solved the issue. it was related to the data type using on the dates fileds in my model. Since I use a master calendar (follow your suggestion) to link with my "tickets" table, and my tickets table has 2 dates fields (Open Date and Close date) I found a situation related to the "close date" field. Some of the tickets are Cancelled, therefore there is no close date in the table, so I have to create a calculate field using the State of each record to figure out if the ticket was cancelled, if so, I'm using Updated date isntead. But, what this cause is that for some reason, I have to change the data type for all the dates field in order to match them as Date (taking away the time portion). At the end and having the same data type for all the date field involve on this the model is working. Hope the explanation can't cause more confution!!

Highlighted
Helper I
Helper I

Re: Cumulative backlog to date

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.

 

image.png

Highlighted
Helper II
Helper II

Re: Cumulative backlog to date

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-

 

Capture.PNG

 

Hope that helps?

Highlighted
New Member

Re: Cumulative backlog to date

Hi @andrew_hardwick,

 

I was reading through this post and tried oud your solution without success.

I think my situation might be slightly different and hoped you could help on this one to :

 

I prepared a simplified version of my raw data (here below)

So basically, i have a ticket on each line.

Each ticket has a opened date, a target for resolution and an actual resolution date 

 

At a given date (today in below example) a ticket is either resolved, late (backlog) or opened and still within time window allowed for resolution.

A ticket may have been late (backlog) at a certain time

 

What i'm trying to achieve:

I would like to set up a cumulative backlog that will show overtime the cumulated backlog and be able to split the line between site A and site B

 

Thanks for your help and your time !

 

Sourceticket IDDate openedTarget closure dateclosed dateis backlogis or has been backlog
Site A101/01/201616/01/201715/01/2017  
Site B201/01/201616/01/2017 18/02/2017yesyes
Site B301/01/201716/01/201718/01/2017 yes
Site A401/02/201716/02/201715/02/2017  
Site A501/02/201716/02/201717/02/2017 yes
Site B601/05/201716/05/2017 16/06/2017yesyes
Site B701/05/201716/05/201715/05/2017  
Site A801/07/201716/07/2017 yesyes
Site A901/07/201716/07/2017 yesyes
Site B1001/07/201716/07/2017 yesyes
Site A915/07/201730/07/2017   
Site B1015/07/201730/07/2017   
Highlighted
Helper I
Helper I

Re: Cumulative backlog to date

@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])))))

 

 image.png

Highlighted
New Member

Re: Cumulative backlog to date

good afternoon, okay? I would like to take a doubt that is making me sleepy already! I discovered the power bi and am madly in love with this software that in my opinion will dominate the world! Where is my problem ??? I'm breaking my head like a madman in order to calculate the backlog in the tool! for example I have projects started from January to now and I need to know what was left open without negotiation. Can you help me please?

 

Highlighted
New Member

Re: Cumulative backlog to date

good afternoon, okay? I would like to take a doubt that is making me sleepy already! I discovered the power bi and am madly in love with this software that in my opinion will dominate the world! Where is my problem ??? I'm breaking my head like a madman in order to calculate the backlog in the tool! for example I have projects started from January to now and I need to know what was left open without negotiation. Can you help me please?

 

Highlighted
New Member

Re: Cumulative backlog to date

good afternoon, okay? I would like to take a doubt that is making me sleepy already! I discovered the power bi and am madly in love with this software that in my opinion will dominate the world! Where is my problem ??? I'm breaking my head like a madman in order to calculate the backlog in the tool! for example I have projects started from January to now and I need to know what was left open without negotiation. Can you help me please?

New Member

Re: Cumulative backlog to date

Hello all,

 

The calculation for the backlog worked perfectly. However, I would like to display the sum of "points" assigned to each ticket instead of the count of the opened tickets, closed tickets, and RT balance.

 

Ticket Number     Opened Date     Closed Date     Points

1234                     01-01-2019        02-01-2019       7

1254                     01-02-2019        01-09-2019       3

5432                     01-31-2019        02-01-2019       1

1456                     01-22-2019        02-08-2019       4

 

So I would like to display the sum of points for the opened, sum of points for the closed, and sum of points for the backlog.

 

Thank you in advance!

Highlighted
Frequent Visitor

Re: Cumulative backlog to date

Hey @Sean , thanks a lot! I found this very helpful. I know the post is kind of old, but it's gold.

 

And I also have a question, I needed the RT Balance, and I also need to find the closed balance from previous periods. For example, this month I resolved three tickets that where created on previous months, I need to show that in the chart along the RT Balance, how would I write a measure for that? I have tried several things, but nothing seems to be working.

 

Any advice or guidance in the right direction would be appreciated. Thanks a lot!

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors