Trying to work out open tickets at the end of months

[jondurbridge]

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

[amitchandak]

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

[jondurbridge · Accepted solution]

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

[TomMartens]

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

[stretcharm]

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/