Otobo ticket report - Backlog per date

[_n_MarianLein]

Hi all,
I'm working on an internal ticket report that should show the open tickets per date.
In my source material I have the following relevant parameters:

• ticket ID - distinct ticket ID
• date_created - date field showing creation of the ticket
• date_changed - date field showing last change to the ticket. may be identical with "date_created"
• state - open, pending, closed successful, closed unsuccessful, merged

There is no field specifically for "date_closed". What I have:

TicketCountPerDayAndStatus = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Opened on date", CALCULATE (
        DISTINCTCOUNT ( 'Otobo Tickets'[id] ),
        FILTER (
            'Otobo Tickets',
            'Otobo Tickets'[date_created] = [Date] &&
            'Otobo Tickets'[Queue Name] <> "specific_queue"
        )
    ),
    "Closed on date", CALCULATE (
        DISTINCTCOUNT ( 'Otobo Tickets'[id] ),
        FILTER (
            'Otobo Tickets',
            'Otobo Tickets'[date_changed] = [Date] &&
            'Otobo Tickets'[Queue Name] <> "specific_queue" && (
                'Otobo Tickets'[State] = "closed successful" ||
                'Otobo Tickets'[State] = "closed unsuccessful" ||
                'Otobo Tickets'[State] = "merged"
            )
        )
    )
)

Which gives me this table:

Now, I want to have a caluclated sum that is showing me all tickets that are open at this date, to show a general trend / backlog in tickets.

So IMHO I would need to sum up all tickets that have been opened until or at the specific date minus all tickets that have been closed until the specific date.

I just can't wrap my head around this, and I feel like I'm wasting my time trying to create this by "distinctcount(something)". There's got to be an easier way than this!?

And yes, I have already searched this forum and tried the solutions pointed out here and here, but they don't work for me, as they are not really applicable to my situation.

[_n_MarianLein · Accepted solution]

Hi all,
I managed to solve the issue with a dedicated table and columns per date. My final (working) code is:

TicketCountPerDay = 
ADDCOLUMNS (
    Calendarauto(),
    "Opened on date", CALCULATE (
        DISTINCTCOUNT ( 'Otobo Tickets'[id] ),
        FILTER (
            'Otobo Tickets',
            'Otobo Tickets'[date_created] = [Date] &&
            'Otobo Tickets'[Queue Name] <> "specific_queue"
        )
    ),
    "Closed on date", CALCULATE (
        DISTINCTCOUNT ( 'Otobo Tickets'[id] ),
        FILTER (
            'Otobo Tickets',
            'Otobo Tickets'[date_changed] = [Date] &&
            'Otobo Tickets'[Queue Name] <> "specific_queue" && 
            (
                'Otobo Tickets'[State] = "closed successful" ||
                'Otobo Tickets'[State] = "closed unsuccessful" ||
                'Otobo Tickets'[State] = "merged"
            )
        )
    ),
    "Open on date", CALCULATE(
        DISTINCTCOUNT( 'Otobo Tickets'[id] ),
        Filter (
            'Otobo Tickets',
            'Otobo Tickets'[Queue Name] <> "specific_queue" &&
            (
                'Otobo Tickets'[date_created] <= [Date] &&
                'Otobo Tickets'[State] <> "closed successful" &&
                'Otobo Tickets'[State] <> "closed unsuccessful" &&
                'Otobo Tickets'[State] <> "merged"
            ) || (
                'Otobo Tickets'[date_created] <= [Date] && (
                    'Otobo Tickets'[State] = "closed successful" ||
                    'Otobo Tickets'[State] = "closed unsuccessful" ||
                    'Otobo Tickets'[State] = "merged"
                ) &&
                'Otobo Tickets'[date_changed] > [Date]
            )
        )
    )
)

[AllisonKennedy]

@_n_MarianLein  You don't need to create a new table, just a new measure, then add to Table visual in the report. You have done number of tickets opened already, just need to tweak it to work as a measure:

 Count Tickets Opened =  CALCULATE (
        DISTINCTCOUNT ( 'Otobo Tickets'[id] ),
        FILTER (
            'Otobo Tickets',
            'Otobo Tickets'[date_created] = SELECTEDVALUE(DimDate[Date]) &&
            'Otobo Tickets'[Queue Name] <> "specific_queue"
        )
)
    

Now you just need to change the

= SELECTEDVALUE( DimDate[Date] )

to a 

<= SELECTEDVALUE( DimDate[Date] )

to get the cumulative count.

You can do the same thing for closed tickets, then subtract the two measures. 

[_n_MarianLein]

Unfortunately, when trying to create a new measure, SELECTEDVALUE([Date]) does not work - It's showing an error "the column 'date' was not found or must not be used in this statement"...

[AllisonKennedy]

@_n_MarianLein  do you have a DimDate table? You'll need that: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Then you can try my previous suggestion. 

[_n_MarianLein]

OK, creating the DimDate table worked so far, so I now have the count of tickets opened on a specific date.
Additionally, I can get the count of tickets closed on a specific date.

Now, how can I correlate these two measures, showing me the open tickets per date?

[amitchandak]

@_n_MarianLein , Can you share sample data.

Check the attached file and these two blogs

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[_n_MarianLein]

Hi,
Attached is some sample data from the ticket system...

The values I expect are:

Date	Opened on date	Closed on date	Cumulative Open on date	<explanation>
2022-01-01	2		2	opened today
2022-01-02	1		3	open yesterday + opened today - closed yesterday
2022-01-03	16	8	19
2022-01-04	8	10	19
2022-01-05	6	5	15
2022-01-06			10
2022-01-07	9	7	19

...

Sample data:

id	state	queue	date_created	date_changed
12855	closed successful	otherQueueName	2022-01-01	2022-01-03
12856	merged	otherQueueName	2022-01-01	2022-01-03
12857	closed successful	otherQueueName	2022-01-02	2022-01-04
12858	closed successful	otherQueueName	2022-01-03	2022-01-04
12859	closed successful	otherQueueName	2022-01-03	2022-01-04
12860	closed successful	specific_queue	2022-01-03	2022-02-04
12861	closed successful	otherQueueName	2022-01-03	2022-01-04
12862	closed successful	otherQueueName	2022-01-03	2022-01-03
12863	closed successful	otherQueueName	2022-01-03	2022-01-03
12864	closed successful	otherQueueName	2022-01-03	2022-01-03
12865	closed successful	otherQueueName	2022-01-03	2022-01-04
12866	closed successful	otherQueueName	2022-01-03	2022-01-03
12867	closed successful	otherQueueName	2022-01-03	2022-01-03
12868	closed successful	otherQueueName	2022-01-03	2022-01-20
12869	closed successful	otherQueueName	2022-01-03	2022-01-03
12870	closed successful	otherQueueName	2022-01-03	2022-02-04
12871	closed successful	otherQueueName	2022-01-03	2022-01-21
12872	closed successful	otherQueueName	2022-01-03	2022-01-04
12873	closed successful	otherQueueName	2022-01-03	2022-01-10
12874	closed successful	otherQueueName	2022-01-04	2022-01-04
12875	closed successful	otherQueueName	2022-01-04	2022-01-04
12876	closed successful	otherQueueName	2022-01-04	2022-01-13
12877	merged	otherQueueName	2022-01-04	2022-01-04
12878	closed successful	otherQueueName	2022-01-04	2022-01-04
12879	closed successful	otherQueueName	2022-01-04	2022-01-07
12880	closed successful	otherQueueName	2022-01-04	2022-01-05
12881	closed successful	otherQueueName	2022-01-04	2022-01-05
12882	closed successful	otherQueueName	2022-01-05	2022-01-05
12883	closed successful	otherQueueName	2022-01-05	2022-01-07
12884	closed successful	otherQueueName	2022-01-05	2022-01-14
12885	closed successful	otherQueueName	2022-01-05	2022-02-03
12886	closed successful	otherQueueName	2022-01-05	2022-01-05
12887	closed successful	otherQueueName	2022-01-05	2022-01-05
12888	closed successful	otherQueueName	2022-01-07	2022-01-07
12889	merged	otherQueueName	2022-01-07	2022-01-07
12890	merged	otherQueueName	2022-01-07	2022-01-07
12891	merged	otherQueueName	2022-01-07	2022-01-07
12892	closed successful	otherQueueName	2022-01-07	2022-01-07
12893	closed successful	otherQueueName	2022-01-07	2022-01-17
12894	closed successful	otherQueueName	2022-01-07	2022-01-12
12895	closed successful	otherQueueName	2022-01-07	2022-01-28
12896	closed successful	otherQueueName	2022-01-07	2022-01-10
12897	closed successful	otherQueueName	2022-01-08	2022-01-10
12898	closed successful	otherQueueName	2022-01-09	2022-01-10

[_n_MarianLein]

@amitm - Have you had a chance to look at this?