Calculate total open tickets at specific dates

[Anonymous]

Info: Using Direct Query, Star schema, fact table with one row per status-change for any ticketID

Hello! 

I'm having a very hard time calculating the number of "current" tickets at a specific date (dates picked using date slicer). 

I have a fact table containing the following "ticketID", "regID", "regDate", "ticketStatus"

One ticketID can have multiple rows in the fact-table, depending on how often it changes status. Status can be "open", "in process", "paused" or "closed". 

I need to be able to look at any date and get a measure showing how many current tickets (status = "open" or "in process") were in the system at that time. How can this be achieved in DAX? (I've seen some calculated colums-solutions, but Direct Query has 1M limit). 

RegID is allways larger based on when it is recieved in the system, so its the optimal to sort out the newest row for any tickedID (regDate is no good as multiple status changes can be done at the same day). 

In other words: I'm looking for the count of [ticketStatus] = ("open" or "in progress") at specific date based on MAX [regID] for each [ticketID]

Is this possible using DAX in a measure? 

If not, could it be done using any tricks in the query editor?

Here is an example. Lets say I'm looking for current tickets at date 15. jan. 2020 the number should be: 2 

ticketID	regID	regDate	ticketStatus
3301	81401	03.jan.2020	Open
3301	81403	06.jan.2020	In Progress
3301	81406	13.jan.2020	Closed
3302	81402	06.jan.2020	Open
3303	81404	09.jan.2020	Open
3303	81405	12.jan.2020	In Progress

Best.

PJ

[v-diye-msft]

Hi @Anonymous 

Kindly refer to the similar threads:

https://community.powerbi.com/t5/Desktop/Calculating-Open-Tickets-at-Specific-Hour/m-p/277385 

https://community.powerbi.com/t5/Desktop/calculate-open-tickets-by-date/m-p/207196 

https://community.powerbi.com/t5/Desktop/Calculate-Number-of-Tickets-Open-at-the-End-of-the-Month/m-p/259410 

If you can't manage it, kindly upload your dummy pbix with the expected results disclosed would be much appreciated.

[Anonymous]

Thanks for the suggested forum posts. 
My dataset differs from most of them in that it has one row per status and no "open" "close" columns in the table it self. 

However, the first forum-post @v-diye-msft liked has a very similar structure as mine and will work with some slight adjustments (I hope). 

Screen clip: 

The only difference from my dataset is that one ticket may have two different "open"-status over the course of its "life" and simply adding a -1 when closed is not sufficient. More specifically, a ticket can have rows displaying status as "Open" and "In Progress". Other tickets may go from "Open" to "Close", without passing through "In Progress". Also some tickets only have "In Progress" then "Close". Some might even only have "Close" without any prior "Open" or "In Progres" (in such case it shouldent be counted at all"). 

Suggestions? 

[v-diye-msft]

Hi @Anonymous 

what's your expecting for the "In progress" tickets? kindly upload your dummy pbix and disclose the expecting results.

[Anonymous]

Hi, again. Thanks for helping @v-diye-msft  & @Greg_Deckler 

Let me refrase the question. I've discovered that I can include a "to-date" column as well that I think can help solving this problem easier. 

Unrelated to my first table in the topic-post, here are another example, highlighting the structure of the table I'm working on. 

To avoid confusion I've changed status names a bit. In this example a ticket is open if Status = "Waiting" OR "In Progress".

My goal is to create a measure that outputs the number of open tickets at specified date set by a slicer. The slicer filters on From Date and should filter the fact table to only get the latest record for each TicketID. This can be obtained by filtering/only counting those rows having To Date = null OR "later than the filtered From Date" (meaning that i was open on that specific date). 

TicketID	Status	From Date	To Date
1	Waiting	1.apr.2020	1.apr.2020
2	Waiting	1.apr.2020	3.apr.2020
1	In Progress	1.apr.2020	3.apr.2020
3	Waiting	1.apr.2020
4	Waiting	2.apr.2020	3.apr.2020
5	Waiting	2.apr.2020	5.apr.2020
6	Waiting	2.apr.2020	5.apr.2020
7	Waiting	2.apr.2020	6.apr.2020
8	Waiting	2.apr.2020	6.apr.2020
1	Closed	3.apr.2020	4.apr.2020
2	In Progress	3.apr.2020
4	In Progress	3.apr.2020	8.apr.2020
5	In Progress	5.apr.2020	5.apr.2020
5	Closed	5.apr.2020
6	In Progress	5.apr.2020
7	In Progress	6.apr.2020	6.apr.2020
7	Closed	6.apr.2020
8	Closed	6.apr.2020
4	Closed	8.apr.2020

So. Lets say I wanted to get all open tickets at From Date = 5.apr.2020

Step-by-step, the measure needs to go through these steps: 

1. filter by From Date: 

TicketID	Status	From Date	To Date
1	Waiting	1.apr.2020	1.apr.2020
2	Waiting	1.apr.2020	3.apr.2020
1	In Progress	1.apr.2020	3.apr.2020
3	Waiting	1.apr.2020
4	Waiting	2.apr.2020	3.apr.2020
5	Waiting	2.apr.2020	5.apr.2020
6	Waiting	2.apr.2020	5.apr.2020
7	Waiting	2.apr.2020	6.apr.2020
8	Waiting	2.apr.2020	6.apr.2020
1	Closed	3.apr.2020	4.apr.2020
2	In Progress	3.apr.2020
4	In Progress	3.apr.2020	8.apr.2020
5	In Progress	5.apr.2020	5.apr.2020
5	Closed	5.apr.2020
6	In Progress	5.apr.2020
7	In Progress	6.apr.2020	6.apr.2020
7	Closed	6.apr.2020
8	Closed	6.apr.2020
4	Closed	8.apr.2020

2. Only count latest record for each TicketID

TicketID	Status	From Date	To Date
1	Waiting	1.apr.2020	1.apr.2020
2	Waiting	1.apr.2020	3.apr.2020
1	In Progress	1.apr.2020	3.apr.2020
3	Waiting	1.apr.2020
4	Waitinged	2.apr.2020	3.apr.2020
5	Waiting	2.apr.2020	5.apr.2020
6	Waiting	2.apr.2020	5.apr.2020
7	Waiting	2.apr.2020	6.apr.2020
8	Waiting	2.apr.2020	6.apr.2020
1	Closed	3.apr.2020	4.apr.2020
2	In Progress	3.apr.2020
4	In Progress	3.apr.2020	8.apr.2020
5	In Progress	5.apr.2020	5.apr.2020
5	Closed	5.apr.2020
6	In Progress	5.apr.2020

Final step: Only count those rows with To Date = null OR date later than filtered From Date

TicketID	Status	From Date	To Date
3	Waiting	1.apr.2020
5	Waiting	2.apr.2020	5.apr.2020
6	Waiting	2.apr.2020	5.apr.2020
7	Waiting	2.apr.2020	6.apr.2020
8	Waiting	2.apr.2020	6.apr.2020
1	Closed	3.apr.2020	4.apr.2020
2	In Progress	3.apr.2020
4	In Progress	3.apr.2020	8.apr.2020
5	Closed	5.apr.2020
6	In Progress	5.apr.2020

Measure should display the number: 7 (as this was the number of tickets that was open (at the end of) 5.april.2020. 

[ccindyp]

Hello, I have exactly the same set up and I am blocked on step 2, I couldn't adapt any post from the community so far. 

Could you please help me sharing the DAX formula please?

"2. Only count latest record for each TicketID"

Thank you so much

[Greg_Deckler]

Maybe try: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364