Count of 'Open' cases over time

[Anonymous]

Hey,

I'm trying to create a line chart over time with the amount of 'open' cases per day. In my data, I have a unique identifying number per case, as well as an 'Issue date' and 'Close date' (or no close date if it is still open). I would like Power BI to calculate how many cases have been open per day, and display them over time. This needs to be calculated for each day over the last two years. 

In the manufactured examples below, I have an example of my data, and an example of the measure/table I need power BI to calculate. 

Can anyone help me with this? Thanks! 🙂

This is the data I will be receiving (and updating every day)This is the information I need to create my 'open cases per day over time'

[hclvenkatakg]

Hi Mecho,

I found your post useful for me as a first step. When we wanted to drill down the Open tickets by other fields in the master table (Tickets), say by 'Region', 'Case Type' etc, the solution of duplicating the 'Tickets' table didn't quite work well for me. I tried to change your solution by using the 'Tickets' table alone without duplicating it. As you said, the inactive relationship will not be effective unless you tell DAX to use it as an active relationship. In the fourth step, I modified the below step from

ClosedSum = SUMX('Calendar','Duplicate Tickets'[ClosedCount])

to 

ClosedSum = SUMX('Calendar',USERELATIONSHIP('Calendar'[Date],'Tickets'[ClosedCount])

 All other steps needs to be followed as per your post. By doing this, slicers could be used from the fields (such as 'Region', 'Case Type' etc from the Tickets table, with which you could visualize the data as per the selected values. 

I thought of posting this solution, as it would be useful to someone who has similar needs as of mine.

[bstark1287]

@hclvenkatakg USERELATIONSHIP doesn't work for me as it says it can only be used in a CALCULATE function. 

[Greg_Deckler]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[Anonymous]

@Greg_Deckler,

I believe that first link is exactly what I need. I've got your original measure plugged in and it seems to be working, but I'm not sure how to tie it into the visual. When I put the 'Tickets Open' into the 'Value' of my chart and the 'Calendar' table I created into the 'Axis' of the visual, it stays blank. There's no relationship between my Calendar table and the query that 'Tickets Open' is in, but I'm not sure how that relationship would work.

I'm relatively new to Power BI, so chances are I've just plugged something into entirely the wrong place.

Here's my measure after I changed the column/query names to match my dataHere's my visual, and you can see how I set up the Calendar table, and the ticket column (here as 'RMA num')

[v-diye-msft]

Hi @Anonymous ,

I think you can refer to the similar post: https://community.powerbi.com/t5/Desktop/ABUSE-By-AlfredR-Board-power-bi-designer-359154/m-p/758431#M365513

[Anonymous]

@Greg_Deckler 

That solution did not quite work for me; it was only able to count tickets that were still unclosed, due to the 'Opened' relationship between the query and the Calendar.

@v-diye-msft 

A modified version of that solution allowed me to create the visual I need; thank you!

[Anonymous · Accepted solution]

I ended up using a number of measures and a line graph to achieve what I wanted:

First of all, I created a calendar table, starting from the date of the first opened ticket until present:

Calendar = CALENDAR(FIRSTDATE('Tickets'[Open Date]),TODAY())

Secondly, I duplicated my original 'Tickets' table. Then, I linked the original 'Tickets' to 'Calendar' via a many-to-one Single relationship through 'Open Date', then linked 'Tickets Duplicate' to Calendar the same way but through 'Close Date'. I tried to use two relationships, one active and one inactive, between 'Tickets' and 'Calendar' for 'Open Date' and 'Close Date', but since only one relationship was active it wasn't able to calculate tickets-closed-per-day. That solution would only give me tickets-opened-per-day, since that was the active relationship.

Then, I created a SUMX function inside 'Tickets' and 'Tickets Duplicate' to count total tickets opened and closed, respectively: 

OpenedCount = COUNT('Tickets'[Ticket Num])
ClosedCount = COUNT('Tickets Duplicate'[Close Date])

Fourth, I made two measures in 'Calendar' to count the total tickets opened and closed per day:

OpenedSum = SUMX('Calendar','Tickets'[OpenedCount])
ClosedSum = SUMX('Calendar','Duplicate Tickets'[ClosedCount])

This is where I needed 'Tickets Duplicate' - without a second active relationship, I could only count tickets opened or closed, based on which of those columns was the active relationship.

Fifth, we needed a "PendingCount" in 'Calendar' to calculate the difference between total tickets opened and total tickets closed to date:

PendingCount = 'Calendar'[OpenedSum] - 'Calendar'[ClosedSum]

Sixth, I added a SUMX function to calculate how many tickets were still pending each day:

Cumulative Total = 
CALCULATE (
    SUMX ( 'Calendar', [PendingCount] ),
    FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

Finally, plotting "Cumulative Total" versus "Date" in a line chart gave me exactly what I wanted.

The reason @v-diye-msft's solution did not work was because it completely eliminated tickets that had been closed. I did get an accurate graph of all of my currently-open tickets over time, but it was unable to give me the historical counts of tickets that were open, day, a year ago, of which all those tickets have been since closed.

Thanks so much for all the help!