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],
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.
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] >