Calculate ammount of open tickets in a given date

[Anonymous]

Hello,

I've been trying for some time to find a way to calculate the amount of open tickets on an arbitrary date, so that I can create a graph of the number of tickets through time. But despite testing different solutions, it hasn't worked so far.

I have two tables:

1. One is a database which contains the date in which tickets are opened, when (or if) they were closed and its current status.
2. The other one is simply a "calendar" that has all the days between 01/01/2010 and today.

My objective is to, for a respective date X in the calendar, count how many tickets were opened at that point in time (so every one whose date opened is <=X and date closed is >X or blank).

Their relationship is as follows:

Here is a minimal working example of the database:

Date Opened	Date Closed	Current Status
11/04/2017	11/05/2018	Closed
15/02/2018		Open
24/11/2018	16/11/2021	Closed
04/02/2019		Open
27/07/2019		Open
24/09/2019	29/10/2020	Closed
12/08/2021		Open

So given these values, the number of tickets would be (filler dates removed):

Day		Number of open tickets
10/04/2017		0
12/04/2017		1
15/02/2018		2
12/05/2018		1
04/02/2019		3
27/07/2019		4
29/09/2019		5
30/10/2020		4
12/08/2021		5
19/10/2022		4

Browsing the forums, I've come across this formula, although it didn't seem to work:

Count = 
CALCULATE(
    COUNT('Database'[Status]),
    Database[Date Opened] <= MIN(Calendar[Day]) &&
    Database[Date Closed] >= MAX(Calendar[Day])
)

Any assistance would be appreciated.

PS.: Here is the graph working on Excel using the formula

=COUNTIFS(Database[Date Opened];"<="&A2;Database[Date Closed];">"&A2) +
COUNTIFS(Database[Date Opened];"<="&A2;Database[Date Closed];"")

with A2 being a date.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Here I suggest you to try this code to create a measure to count status.

Count = 
CALCULATE (
    COUNT ( ' Database'[Current Status] ),
    FILTER (
        ' Database',
        ' Database'[Date Opened] <= MAX ( 'Calendar'[Date] )
            && OR (
                ' Database'[Date Closed] > MAX ( 'Calendar'[Date] ),
                ' Database'[Date Closed] = BLANK ()
            )
    )
) + 0

My Sample:

Calendar = CALENDAR(DATE(2017,01,01),DATE(2022,12,31))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey @v-rzhou-msft, my apologies for the delay in responding.

Thank you so much for the help! Although the code didn't do a cumulative sum initially (it was only displaying the amount of tickets for a given day), adding ALLSELECTED()  to the first FILTER parameter seems to have made it work.

Count = 
CALCULATE (
    COUNT ( ' Database'[Current Status] ),
    FILTER (
        ALLSELECTED(' Database'),
        ' Database'[Date Opened] <= MAX ( 'Calendar'[Date] )
            && OR (
                ' Database'[Date Closed] > MAX ( 'Calendar'[Date] ),
                ' Database'[Date Closed] = BLANK ()
            )
    )
) + 0

Again, thanks for the assistance in this matter.

Best regards,

Tiago Reis