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
