Count of tickets unresolved (open) on each date-Snapshot of past dates

[loverlin]

I am hoping this is posible with Dax in Power BI. I am hoping for some help here as those I have talked to have not been able to help.

I have a table called SystemTickets

SystemTickets Table

I need to create a visual that counts unresolved tickets on specific dates, a snapshot of past dates if you will. I would be able to select a past date and get a count of how many tickets were unresolved (Open). I need to be able to show in a line chart the dates and a count of tickets that were unresolved on each of those dates. 

[Jihwan_Kim · Accepted solution]

Hi,

I suggest having a disconnected calendar table like below picture, and please write the measure like below.

please check the below picture and the attached pbix file.

Unresolved tickets count: =
CALCULATE (
    COUNTROWS ( VALUES ( Data[IDNumber] ) ),
    FILTER (
        Data,
        Data[OpenDate] <= MAX ( 'Calendar'[Date] )
            && OR (
                Data[ResolvedDate] >= MIN ( 'Calendar'[Date] ),
                Data[ResolvedDate] = BLANK ()
            )
    )
)

[Tutu_in_YYC]

Hi Loverlin,

Let me know if this works for you.

Step 1:
Remove time stamps in both OpenDate and ResolvedDate. Do this in Power Query, as changing data type in Power BI desktop may just hide the time, not completely remove it. Make sure they have date type.

Step 2:
All the blanks in ResolvedDate have to be replaced with a date that is waaaayyy in the future. i.e 1/1/2099
We can do this by replace value of blank in Power Query with that date. 

Step 3:
You must have another date table that you can use to choose a past date. Lets call this table Calendar and the column in there is PastDate. This table must have no relationship with any table. You can use DAX to create a date table.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Step 4:
Create a slicer with PastDate

Step 5:
Write this measure:

No of Unresolved Tickets =
VAR SelectedPastDate = SELECTEDVALUE( Calendar[PastDate])
RETURN

CALCULATE(
   DISTINCTCOUNTNOBLANK( SystemTickets[IDNumber]),
   FILTER(
      SystemTickets,
      SystemTickets[OpenDate] <= SelDate &&
      SystemTickets[ResolvedDate] > SelDate
   )
)

Step 6:
Create the line chart and use the above measure and PastDate as axis