Calculating active issues per month

Hi Anonymous,

Please find below a solution approach:

Base table:

Data model:

active relationship between DimDate[Date] and Ticket[CreatedDate]

inactive relationship between DimDate[Date] and Ticket[ClosedDate]

I calculated 4 measures in total:

01 New  Tickets = 
COUNTROWS(Tickets)

02 Total Opened Tickets = 

var var_ReferencePeriod = MAX('Dim Date'[Date])

var var_Calculation =
    CALCULATE(
        COUNTROWS(Tickets),
        ALL('Dim Date'),
        Tickets[Created Date] <= var_ReferencePeriod
    )


RETURN

var_Calculation

03 Total Closed Tickets = 

var var_ReferencePeriod = MAX('Dim Date'[Date])

var var_Calculation =
    CALCULATE(
        COUNTROWS(Tickets),
        ALL('Dim Date'),
        Tickets[Closed Date] <= var_ReferencePeriod,
        NOT ISBLANK(Tickets[Closed Date]),
        USERELATIONSHIP('Dim Date'[Date],Tickets[Closed Date])
    )


RETURN

var_Calculation 

04 Active Tickets = 
[02 Total Opened Tickets] - [03 Total Closed Tickets]

in the end the trick is to calculate all the amount of historically created tickets - historically closed tickets (based on reference date). This is how you get active tickets to reference date

Result in a matrix visual:

to show the measures in rows please use the formatting functionaliry in the format pane for the matrix

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.