How to calculate Backlog Tickets

[ShravanG]

Hey Champs,

I'm new to power BI and trying to find the number of new,closed and  Backlog tickets in my data.

Scenario: I have two tables 

1. Tickets Data - It has ID, Created date, Solved Date and Status column

                           I created new tickets from Created date, Closed tickets from solved date

2. Date column created to establish non-active relationship to calculate new and closed tickets.

Below are the measure I have created for new and closed 

New_Tickets = CALCULATE(COUNT('Tickets Data'[ID]),

    USERELATIONSHIP('Tickets Data'[CREATED_AT], 'Date'[Date]))
________________________________________________________________________________

Closed_Tickets = CALCULATE(COUNT('Tickets Data'[ID]),

    USERELATIONSHIP('Tickets Data'[SOLVED_AT], 'Date'[Date]))

________________________________________________________________________________ 

Now I have calculated backlog for latest month as below 

In this case theoritically the backlog is calculated as:
if we are calculating it for month of February then
(the tickets should be created before february) and ((the tickets should not fall in closed, solved or deleted status)  OR (solved date should be in february or after))

1A_EOM_Backlog =

VAR MaxDate = DATE(YEAR(Today()), month(today()), 1)

VAR Result =

CALCULATE(

COUNT(Tickets Data[ID]),

'Tickets Data'[CREATED_AT] < MaxDate &&

 (NOT('Tickets Data'[STATUS] = "Closed" ||

      'Tickets Data'[STATUS] = "Solved" ||

      'Tickets Data'[STATUS] = "Deleted") ||

        'Tickets Data'[SOLVED_AT] >= MaxDate)

)

Return Result  

 ___________________________________________________________________________

I also calculated difference

 Difference = [New_Tickets] - [Closed_Tickets]

____________________________________________________________________________

I'm unable to write measure for the below (Bold , underlined) to calculate the number of backlog tickets for previous months. Can anyone please help me with this. @Greg_Deckler @TomMartens @amitchandak 

From Date table	Measures created
Year	Month	New_Tickets	Closed_Tickets	Difference	Backlog Tickets
2022	August	4811	4783	28	373
2022	September	4708	4759	-51	322
2022	October	4531	4509	22	344
2022	November	4124	4137	-13	331
2022	December	4135	4101	34	365
2023	January	4569	4518	51	416

pls let meknow if you need any other information

[ShravanG]

Thank you for the replies, the problem has been solved now.

[Ashish_Mathur]

Hi,

Share the raw dataset to work with and show the expected result.

[amitchandak]

@ShravanG , seem very similar to HR problem

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU