cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jondurbridge Helper I
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
jondurbridge Helper I
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

View solution in original post

4 REPLIES 4
Super User III
Super User III

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

Hey @jondurbridge ,

 

my data model looks like:

image.png

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
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
stretcharm Memorable Member
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
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
Connect on Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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


jondurbridge Helper I
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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

Community Summit North America

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

Top Solution Authors