cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Total Count overall on a last 30 days charts

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@amitchandak 

 

I figured it out.

 

Open Tickets = 

CALCULATE(DISTINCTCOUNT(Help Desk Activity[TicketId]),
FILTER(Help Desk Activity, Help Desk Activity[CreatedDate] <= MAX(Date Calendar[Date])
&& (ISBLANK(Help Desk Activity[ResolvedDate]) || Help Desk Activity[ResolvedDate] >
MAX(Date Calendar[Date]))))
 
Thanks for your help. The article helped give me the idea.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

@nleuck , not sure I got the exact problem. Refer to a similar blog, you should able to plot it by day

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@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.






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.





Highlighted

@amitchandak 

 

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.

 

Open Tickets =
CALCULATE(COUNTX(FILTER(Help Desk Activity, Help Desk Activity[CreatedDate] <= MAX(Date Calendar[Date])
&& (ISBLANK(Help Desk Activity[ResolvedDate]) || Help Desk Activity[ResolvedDate] >
MAX(Date Calendar[Date]))), (Help Desk Activity[TicketId])))
Highlighted

@amitchandak 

 

I figured it out.

 

Open Tickets = 

CALCULATE(DISTINCTCOUNT(Help Desk Activity[TicketId]),
FILTER(Help Desk Activity, Help Desk Activity[CreatedDate] <= MAX(Date Calendar[Date])
&& (ISBLANK(Help Desk Activity[ResolvedDate]) || Help Desk Activity[ResolvedDate] >
MAX(Date Calendar[Date]))))
 
Thanks for your help. The article helped give me the idea.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors