Visualization, count of Ticket's Status in historical context

[Fokr]

Hi, I’m fairly new to Power Bi, but for most of the things I could find a solution except for this mindcracker.  

Data Sample is in the link on OneDrive. [https://1drv.ms/u/c/......] 

I need to visualize a count of tickets [ID] that had a specific status on a particular day on a chart.

Status is determent as the latest between other 3, but no more than the day we are looking at. 

So the backlog [Ticket_In]on 10/1/23 was 10, till 12th  there were no changes but 5 new came in =15 on 12/1/23.

On 13th they all had a new datestamp under [Work_IN] there for on 13/1/23 line for [Ticket_In] shows zero, and [Work_In] 15

Let's concentrate only on calculating status [Ticket_IN]:

measure 1 = calculate(count(data[Ticket_IN]),

(not isblank('data'[Ticket_IN])

,(selectedvalue(Calendar[date])<data[Work_IN] || data[Ticket_IN] > data[Work_IN]))

/*repeat last line 3x for a check against other statuses*/

I have tried different approaches with Filter(); ALL(Calendar[date]); max instead of selectedvalue; tried moving date part for check out as a VAR...

Something is missing and in a week i, nor AI fount it, please help, point in the right direction.

More about the data: Ticket_in can be used as whole timeline, but in real dataset it also can be empty. Those are the timestamps of when a ticket had been confirmed last time in under a particular status. 

[rajendraongole1]

Hi @Fokr - You can create a DAX measure that will calculate the ticket count based on conditions. Here’s an example focusing on the Ticket_IN status.

Ticket_IN_Count =
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
RETURN
CALCULATE(
COUNTROWS('data'),
'data'[Ticket_IN] <= SelectedDate,
(
ISBLANK('data'[Work_IN]) || 'data'[Work_IN] > SelectedDate
),
(
ISBLANK('data'[Work_OUT]) || 'data'[Work_OUT] > SelectedDate
),
(
ISBLANK('data'[Ticket_OUT]) || 'data'[Ticket_OUT] > SelectedDate
)
)

as you mentioned,  For Work_IN, Work_OUT, and Ticket_OUT, you can create similar measures with modified conditions. Adjust the DAX to ensure it only counts tickets that are active under each status based on the latest timestamp. now you can use line chart with x-axis date and values for each session of chart

Hope this helps.

[Fokr]

Hi rajendraongole1! 
Sadly, your solution didn't work for me. 
I have tried many variations, the best I got was:

• the count of currently open tickets, but the chart in this case only shows when those tickets were opened. 
• the count of left opened tickets on a particular date, but not for the period prior 

Any attempt in counting for a period resulted in a cumulative count totaling in thousands (in sample data 1K), when the expected value is around 30-100.

Especially challenging are cases when status change occurred on the same day.

So that: 

'data'[Work_OUT])  > SelectedDate

doesn't really work, because 

SelectedDate = 10.01.2024 00:00

while    'data'[Ticket_IN]) = 10.01.2024 08:50

and      'data'[Work_OUT]) = 10.01.2024 8:54

All help is appreciated, tho, thank you