Total Count overall on a last 30 days charts

[nleuck]

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!

[amitchandak]

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

[nleuck]

@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])))

[nleuck]

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

[parry2k]

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