Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Folks,
I have been suffering to get the correct Open Ticket counts for specific dates. I need to show total open ticket trend with line chart. We need all the tickets which are created and not closed on specific date.
Even tickets created and closed on the same date that should be consider as open tickets.
Below is the relation I am have.
These are my measures:-
Cases Created = DISTINCTCOUNT(Incident[ticketnumber])
Cases Resolved = CALCULATE(Incident[Cases Created],USERELATIONSHIP('Date'[Date],Incident[Day Completed]))
Cases Calculation = [Cases Created] - [Cases Resolved]
Total_Open_Tickets =IF (ISBLANK ( [Cases Created] ) && ISBLANK ( [Cases Resolved] ),BLANK (),
CALCULATE ([Cases Calculation],FILTER (ALL ( 'Date' ),'Date'[Date] <= MIN ( 'Date'[Date] )&& 'Date'[Date] <> BLANK ())))
We have also tried other community links but didn't get a solution.
Hi @redsailbi ,
Whether your problem has been resolved? If yes, could you please mark the post as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.
Otherwise, you can create a measure as below to get the count of open cases over time:
Count of Open cases =
VAR _curdate =
SELECTEDVALUE ( 'Date'[Date] )
VAR _opencases =
CALCULATE (
DISTINCTCOUNT ( Incident[ticketnumber] ),
ALL ( Date ),
'Date'[Date] <= _curdate,
ISBLANK ( Incident[Day Completed] )
|| Incident[Day Completed] >= _curdate
)
RETURN
_opencases
In addition, you can refer the content in the following links to achieve it.
Count of Open or Closed items based on 2 different Date Columns
Calculate OPEN CASES over time in Power BI
Best Regards
@redsailbi , Refer My blog on similar topic
Current employee same as Open ticket