Calculate Number of Tickets Open at the End of the Month

[Karen_Payne]

Requesting help from the awesome PBI Community with a probable combination of DAX formulas  

I’ve been requested to calculate the number of carryover tickets month over month for our Service Desk Department.  Carryover, in our case, is defined as counting the number of open tickets at the end of the month (regardless of when they were opened).  Ticket are considered open when the Closed Date field is null. 

Additionally, from this monthly cohort, I would like to identify how many tickets were opened 30 ago and 60 days ago.  In a perfect world, I would like to add two lines to the graph below, one for # of tickets open more than 30 days, and another for the # of tickets opened 60 days ago.

Lastly, there are two different types of tickets – Service Requests and Incidents, which I would need counted separately. 

Thank you in advance for your help!

Karen Payne

Mock-up data example:

[Kris_Herbison]

Thanks for this solution.  

I have been checking this with my data, and I found that the following change was needed as items closed on the first of the following month were being excluded from the data.

[Date] <= IF([Closed Date] = BLANK(),"2099-01-01",[Closed Date])

Instead of:

 [Date] < IF([Closed Date] = BLANK(),"2099-01-01",[Closed Date])

Thanks

[Phil_Seamark · Accepted solution]

Hi @Karen_Payne

I think this calculated table might be getting close....  Just replace the highlighted 'Table2' with your own table name.

New Table = 
SELECTCOLUMNS(
FILTER(
    CROSSJOIN(
        ADDCOLUMNS(
        FILTER(
            CALENDAR("2017-01-01",TODAY()),
            day([Date])=1
            ),
            "Reporting Month",var d = [Date]-1 RETURN DATE(YEAR(d),MONTH(d),1)
            
            ),
        'Table2'),
        [Date] > [Create Date] && 
        [Date] < IF([Closed Date] = BLANK(),"2099-01-01",[Closed Date])
        ) ,
        "Reporting Month" , [Reporting Month] ,
        "ID" , [ID] ,
        "Ticket Type" , [Ticket Type]
)

I get the following data from it

[Anonymous]

How can this be done in a ssas tabular model ?Could'nt be done as a measure without having to create a table

[Karen_Payne]

Thank you.  Worked like a charm.