cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Trying to work out open tickets at the end of months

Hi All,

I'm struggling to get my head around this. I have a ticketing system, and what I need to try and work out is how many tickets were open at the end of each month.

Tickets are calculated by a distinctcount of ticket number

Each ticket has a create date

Each tickets has a complete date (or blank if not completed)

I have a separate table for Date which has relationships with completed and created times.

I am trying to produce a graph where the axis is the date, and then display tickets that were open in each month

I need something along the lines of

Calculate(DistinctCount(Tasknumber),createtime<=month and complete time is blank or >month)

I cant seem to get this working

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Helper I

## Re: Trying to work out open tickets at the end of months

I'm really sorry.

After raising this topic it showed me reccomendations.

https://community.powerbi.com/t5/Desktop/Calculate-Number-of-Tickets-Open-at-the-End-of-the-Month/m-...

That one above nailed exactly what I needed.

Kudos

4 REPLIES 4
Super User III

## Re: Trying to work out open tickets at the end of months

Hey @jondurbridge ,

my data model looks like:

There are relationship between

• date and invoice date key
• date and delivery date key

It's essential to know, that the invoice date key corresponds to your created time and the delivery date key corresponds to your completed time.

Here is the measure that counts the open sales keys:

``````no of open saleskeys =
var datemax = CALCULATE(MAX('Dimension Date'[Date]))
return
CALCULATE(
SUMX(
FILTER(
'Fact Sale'
,
('Fact Sale'[Invoice Date Key] <= datemax && ISBLANK('Fact Sale'[Delivery Date Key]))
||
('Fact Sale'[Invoice Date Key] <= datemax && 'Fact Sale'[Delivery Date Key] > datemax)
)
, 1
)
, ALL('dimension Date')
, CROSSFILTER('Dimension Date'[Date] , 'Fact Sale'[Delivery Date Key] , None)
, CROSSFILTER('Dimension Date'[Date] , 'Fact Sale'[Invoice Date Key] , None)
)``````

Hopefully, this provides what you are looking for. If not please consider to provide a pbix that contains sample data and reflects your data model. Upload the file to onedrive or dropbox and share the link.

Regards,

Tom

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Memorable Member

## Re: Trying to work out open tickets at the end of months

Alberto Ferrari has a video with a use case for Open Orders that is similar to your needs.

See this video starting at minute 50

https://www.sqlbi.com/tv/dax-optimization-examples/

Super User IV

## Re: Trying to work out open tickets at the end of months

Refer to the article on the same line: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Helper I

## Re: Trying to work out open tickets at the end of months

I'm really sorry.

After raising this topic it showed me reccomendations.

https://community.powerbi.com/t5/Desktop/Calculate-Number-of-Tickets-Open-at-the-End-of-the-Month/m-...

That one above nailed exactly what I needed.

Kudos

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors