Forum Discussion
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])))
Created, Resolved, Backlog
I hope this works for you!
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])))
Created, Resolved, Backlog
I hope this works for you!
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?
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 between ddates
Then your results will certainly be correct!
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?
January Feb Support Group Opened Count Resolved Count Backlog Opened Count Resolved Count Backlog L1 Support 100 50 50 100 50 50 L2 Support 123 23 100 123 23 100
Thanks, this is great!
