cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vtechzpbi Member
Member

Running Sum in Power BI for Ticket Backlog

I have IT Tickets data with below shown columns

  1. Ticket Number
  2. Ticket Status
  3. Ticket CreatedDate
  4. Ticket ResolvedDate

I would like to create a report like below...

 

MonthCreated CountResolved CountBacklogCalculation
   100<- Backlog
Jan-195040110<- 100+50-40
Feb-1910080130<- 110+100-80
Mar-19200100230 

 

I have the raw data and PBIX in one drive for you review.

 

https://1drv.ms/u/s!AuZ8zsEu-lf-aKZZdq9ll8lX0JA?e=Z93Vf4

 

Problem is I have done this in Tableau and QlikView in just a right click on mouse. But I have no clue on how to achieve these three metrics in a single table in Power BI. I have the similar requirement to show the same table by IT Team list instead of Month list etc.,

 

You help is greatly apreciated

1 ACCEPTED SOLUTION

Accepted Solutions
carlomoretto Frequent Visitor
Frequent Visitor

Re: Running Sum in Power BI for Ticket Backlog

Hi!

 

I think you could make a calendar table and establish relationships between the calendar table date and the creation and resolution dates. You can then use these relationships to compute the measures you need.

 

Here's what I've done.

 

Created a calendar table called DimDate using CALENDARAUTO()

Created relationships between the Date variable from the Calendar table and the Creation and Resolution Date.

 

I then created the 3 measures:

 

Created Count = CALCULATE(COUNT(data[ID]))

Resolved Count = CALCULATE(COUNT(data[ID]);USERELATIONSHIP(data[Resolution Date];DimDate[Date]);data[Resolution Date]<>BLANK())

Backlog = CALCULATE([Created Count]-[Resolved Count];FILTER(ALLSELECTED(DimDate);DimDate[Date]<=max(DimDate[Date])))

 

Relationships.PNGCreated, Resolved, Backlog

 

I hope this works for you!

View solution in original post

7 REPLIES 7
carlomoretto Frequent Visitor
Frequent Visitor

Re: Running Sum in Power BI for Ticket Backlog

Hi!

 

I think you could make a calendar table and establish relationships between the calendar table date and the creation and resolution dates. You can then use these relationships to compute the measures you need.

 

Here's what I've done.

 

Created a calendar table called DimDate using CALENDARAUTO()

Created relationships between the Date variable from the Calendar table and the Creation and Resolution Date.

 

I then created the 3 measures:

 

Created Count = CALCULATE(COUNT(data[ID]))

Resolved Count = CALCULATE(COUNT(data[ID]);USERELATIONSHIP(data[Resolution Date];DimDate[Date]);data[Resolution Date]<>BLANK())

Backlog = CALCULATE([Created Count]-[Resolved Count];FILTER(ALLSELECTED(DimDate);DimDate[Date]<=max(DimDate[Date])))

 

Relationships.PNGCreated, Resolved, Backlog

 

I hope this works for you!

View solution in original post

vtechzpbi Member
Member

Re: Running Sum in Power BI for Ticket Backlog

Capture2.PNG

 

Thanks for replying. I tried the steps but not able to replicate the same chart as you did..

 

Can you help me with your PBIX..?

 

I can also see Creation Date and Resolution Date columns in your screenshot.. But I dont have those.. AM i facing the issue because of this..?

 

I created relationship like DimDate Date = Date Created (Many to Many and Single)... I did anything wrong?

 

 

carlomoretto Frequent Visitor
Frequent Visitor

Re: Running Sum in Power BI for Ticket Backlog

Hi! 

 

Once you have the time table, you just have to create the relationship between the Date of the time table and the creation date and resolution date. Just make sure all dates are in the same format (I've set them to dd/MM/yyyy). Since you're interested in the date without hours and minutes, you can also extract them in a new column, like so:

 

Creation Date = data[Created].[Date]

Resolution Date = data[Resolved].[Date]

 

You then establish the relationships as shown in the screen below:

First you drag and drop the Date field from the DimDate table to the Creation Date field. This creates an active 1 to Many relationship between the Date and the Creation Date.

 

Then you drag and drop the Date field from the DimDate table to the Resolution Date field. This creates an inactive 1 to Many relationship between the Date and the Resolution Date. This is the relationship that we activate when using the USERELATIONSHIP function in the measure for the Resolved Count.

 

Relationships.PNGRelationships between ddates

Then your results will certainly be correct!

 

vtechzpbi Member
Member

Re: Running Sum in Power BI for Ticket Backlog

Thanks a lot. Now i followed the instructions and able to replicate the same visual..

 

All  calculation seems to be fine except for January (Backlog 224 + Created 6644 - Resolved 5639) so Jan backlog is 1229. It is showing 781.

 

Also if I have a Support Team column (L1 Support, L2 Support) in the data. And if I need a visual like below.. Is it possible?

 

 JanuaryFeb
Support GroupOpened CountResolved CountBacklogOpened CountResolved CountBacklog
L1 Support10050501005050
L2 Support1232310012323100
carlomoretto Frequent Visitor
Frequent Visitor

Re: Running Sum in Power BI for Ticket Backlog

Sorry, my mistake, I forgot to exclude the blank dates from the Resolved Count measure:

 

Resolved Count = CALCULATE(COUNT(data[ID]);USERELATIONSHIP(data[Resolution Date];DimDate[Date]);data[Resolution Date]<>BLANK())
 
That should do
 
You can get the table you're looking for by simply creating a Matrix visual, where you will put the Date on the Columns, the Groups on the rows and the three measures on the values.
 
 
vtechzpbi Member
Member

Re: Running Sum in Power BI for Ticket Backlog

Thank you so much.. YOU are awesome....

vtechzpbi Member
Member

Re: Running Sum in Power BI for Ticket Backlog

@carlomoretto  My friend the solution is working perfectly. One more help.

 

In our solution we were using the data fields as DATE ONLY. If I change it to DateTime then the results are not coming up. Is there any ways to use the date fields as DateTime with the same solution?

 

Reason is I have to bring a count like below...

                   Opened      Closed    Backlog (Carry to next shift)

Shift 1        100              20              80

Shift 2         80+50       60              70

Shift 3         70+10       10              70

 

Where Shift 1 is 6 am to 2 pm, Shift 2 is 2pm to 10 PM and Shift 3 is 10 PM to 6 AM

80+50 and 70+10 means - 80 came from Shift 1 and 50 new tickets got created in shift 2.

 

You idea will help me a lot....

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 51 members 1,039 guests
Please welcome our newest community members: