Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NickMorrell
Frequent Visitor

Running total for number of open tickets?

I have a table with data about particular tickets. The columns are:

 

TicketId | CreatedDate | ClosedDate | CurrentStatus |

 

 

I'm trying to work out how I can get a total per month of how many open tickets there were at month end.

 

For example, in the month of February there might have been 300 tickets that were opened, and there were still 250 left over from the month before, then 100 were closed over the course of February, the number for the month of February should be the nett open tickets at the end of the month, i.e. 300 + 250 - 100 = 450 tickets.

 

Then the 450 gets carried to March, and n tickets were opened in March, minus x tickets that were closed, etc.

 

Either total by month or total by date is fine. Thanks.

3 REPLIES 3
amitchandak
Super User
Super User

You need to have a calendar dimension. You need to join it both open date and close date. Where join with close date will be inactive.

 

Then try something like this

 

Active employee = 
Var   _start_date=(minx('Date','Date'[Date])) 
Var   _end_date=(maxx('Date','Date'[Date])) 

return
CALCULATE (count(ticket),open_date <= _end_date) -CALCULATE (count(ticket),close_date <= _end_date,userelation(date[date],ticket[close_date]))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

 

Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @NickMorrell 

I have created a sample pbix based on your requirement. See Download Link: Sample - Tickets.pbix

 

I used the Power query to create a function to generate the running balance. Please refer to the Edit Queries>Advance Editor for the code.

 

PJ

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.