Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So i have two columns that are both filled with Dates, one Column has the date that a ticket was submitted and the other has the date a ticket was completed. I need to be able to get the Data for how many tickets are open on a current date. For example, if 3 tickets were open today, and none were closed the total would be 3, if 2 tickets were opend the next day and 4 were closed the running total would be 1. Does anyone have any idea how I would be able to accomplish this? Thank you!!
Solved! Go to Solution.
You can consider this one.
CountOfOpenTicketsParamMeasure =
IF ( MAX ( 'Date'[Date]) <= MAX(Ticket[Opened] ),
COUNTROWS (
FILTER (
Ticket,
Ticket[Opened] <= MAX('Date'[Date]) &&
( Ticket[Closed] > MAX('Date'[Date]) || ISBLANK( Ticket[Closed] ))
)
)
)
This is yet to be optimized. Hope this helps.
I have 2 columns, one was the request date that a ticket was put in, the other was a completion date where the ticket was finished.
I need to be able to have a running total of the amount of open tickets. For example
Date Start | Date End | Tickets to Date
1/1/18 | 1/2/18 | 2
1/1/18 | 1/2/18 | 2
1/2/18 | 1/2/18 | 3
1/3/18 | 1/7/18 | 1
Does anyone Have any idea how functionality like this could be accomplished?
I am tring to get the Measure working as I think what you have done is what I need. I get an error as follows:
MdxScript(Model) (26, 69) Calculation error in measure 'ServiceTickets'[mCountOfOpenTickets]: A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.
The column I am looking at is the Date Entered column for the ticket.
Thanks in advance.
You can consider this one.
CountOfOpenTicketsParamMeasure =
IF ( MAX ( 'Date'[Date]) <= MAX(Ticket[Opened] ),
COUNTROWS (
FILTER (
Ticket,
Ticket[Opened] <= MAX('Date'[Date]) &&
( Ticket[Closed] > MAX('Date'[Date]) || ISBLANK( Ticket[Closed] ))
)
)
)
This is yet to be optimized. Hope this helps.
If you just want to get the count of open tickets as of today, you can consider this measure
CountOfOpenTicketsAsOfToday =
CALCULATE ( COUNTBLANK (Ticket[Closed] ), Ticket[Opened] <= TODAY() )
If yout want to get the data for each selected date, you can use either a calculated column or a measure
CountOfOpenTicketsParamMeasure =
IF ( MAX ( 'Date'[Date]) <= MAX(Ticket[Opened] ),
COUNTROWS (
FILTER (
Ticket,
Ticket[Opened] <= MAX('Date'[Date]) &&
( Ticket[Closed] > MAX('Date'[Date]) || ISBLANK( Ticket[Closed] ))
)
)
)
CountOfOpenTicketsCalColum =
COUNTROWS (
FILTER (
Ticket,
Ticket[Opened] <= EARLIER(Ticket[Opened]) &&
( Ticket[Closed] > EARLIER( Ticket[Opened]) || ISBLANK( Ticket[Closed] ))
)
)
This is yet to be optimzed. Hope this helps
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |