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.
Hello All,
I'm working on a report on our Help Desk activity. I have two tables: Help Desk Activity and Date Calendar. I have three measures that find total count of Tickets Created, Tickets Resolved, and Tickets Open.
Tickets Created = CALCULATE(DISTINCTCOUNT(Help Desk Activity[TicketId]), USERELATIONSHIP(Help Desk Activity[CreatedDate],
Date Calendar[Date]))
Tickets Resolved = CALCULATE(DISTINCTCOUNT(Help Desk Activity[TicketId]), USERELATIONSHIP(
Help Desk Activity[ResolvedDate], Date Calendar[Date]))
Tickets Open = CALCULATE(DISTINCTCOUNT(Help Desk Activity[TicketId]), USERELATIONSHIP(Help Desk Activity[CreatedDate],
Date Calendar[Date]), ISBLANK(Help Desk Activity[ResolvedDate]))
I have a bar chart that shows Tickets Created and Tickets Resolved over the last 30 days. The last 30 days is a column in my Date Calendar that marks the days if they are within the last 30 days. But Tickets Open they want to show the Total Open Tickets as of the day on the last 30 days chart. For example:
If there were a total of 30 Open Tickets as of June 8th and on June 9th there were two more open tickets, June 9th would show 32 Open Tickets and not just 2 open tickets for that day. If June 10th there were 3 more Tickets Resolved than Tickets Created then June 10th would show 29 Open Tickets.
I'm trying to get the overall total as of that day to show on the last 30 days bar chart. FYI, I'm not allowed to share my data.
Thanks in advance for your help!
@nleuck , not sure I got the exact problem. Refer to a similar blog, you should able to plot it by day
I think your solution is close. The problem is the total count is 24 higher than my actually count. As of June 14th I had 52 Open Tickets. I used the forumula that was used to calculate current employees in the article you provided and it shows June 14th as 76 Open Tickets. Some tickets may have multiple rows so I need a more of a distinct count on ticket id. I tried to place DISTINCTCOUNT on TicketId at the end of the measure, but my results didn't change.
I figured it out.
Open Tickets =
@nleuck seems like you are looking for running total of [Tickets Open] - [Tickets Resolved]
RT =
SUMX (
FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ),
[Open Tickets] - [Closed Tickets] )
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |