Creating a PowerPivot measure in DAX to calculate tickets backlog

[Anonymous]

I started to work recently with DAX in both PowerPivot for Excel and PowerBI, and there's something I used to achieve with an Excel macro and now I can't seem to get it to work with DAX, I think it's out of my reach at the moment.

I need to calculate the open tickets from other months that enter into the current month, the total tickets from other months that are being solved in the current month, and the percentage of tickets of those backlog tickets I solved in the current month.

I get my data via SQL query from a MariaDB database, and for this case the most relevant fields are Ticket, Creation Date and End Date.

So when I was doing this in excel, with a macro I just created a new table with duplicate values for each ticket that got into a new month. For example, I got a ticket from august 2019 and it was closed on january 2020, so in the new table I would get 5 records for this ticket (september, october, november, december and january). And for the currently open tickets I used the current date as end date. So in that way I created a pivot table from the resulting data of the macro, getting the results I needed to show.

This is the macro:

Sub Backlog()

    Dim matrix As Variant
    Dim lRow As Long
    Dim lCol As Long
    Dim i As Integer
    Dim count As Integer
    Dim difm As Integer
    count = 0

    With ThisWorkbook.Sheets("Data")
        lRow = .Range("A" & .Rows.count).End(xlUp).Row
        lCol = .Cells(lRow, .Columns.count).End(xlToLeft).Column
        matrix = .Range(.Cells(2, 1), .Cells(lRow, lCol)).Value
    End With

    For lRow = LBound(matrix, 1) To UBound(matrix, 1)
        If matrix(lRow, 14) = vbNullString Then matrix(lRow, 14) = CDbl(Now())
        If IsNumeric(matrix(lRow, 13)) Then
            difm = DateDiff("m", CDate(matrix(lRow, 13)), CDate(matrix(lRow, 14)))
            If difm > 0 Then
                For i = 1 To difm
                    With ThisWorkbook.Sheets("Backlog")
                        .Range("A2").Offset(count, 0).Value = matrix(lRow, 1)
                        .Range("A2").Offset(count, 1).Value = matrix(lRow, 2)
                        .Range("A2").Offset(count, 2).Value = Format(DateAdd("m", i, matrix(lRow, 13)), "yyyy-mm-mmm")
                        .Range("A2").Offset(count, 3).Value = matrix(lRow, 9)
                        .Range("A2").Offset(count, 4).Value = matrix(lRow, 10)
                    End With
                    count = count + 1
                Next i
            End If
        End If
    Next lRow

End Sub

This is a sample of the resulting data from the macro (I removed the extra fields):

And from the pivot table I created this chart:

I know the basics about DAX and I have tried several things, with relationships to my calendar table, but I don't get the desired results. I'm not posting what I have tried in PowerPivot, since what I tried didn't get me close enough to what I needed. And the info I found in the internet about this, was directed towards different needs.

Thanks a lot for the help you can provide, or any guidance in the right direction. If extra info is needed I will provide. Thanks!

[JustJan]

Hi @Anonymous ,

You could add a calculated column to your table, with the switch command. This will calculate the ticket status at record level.

It could be something like: 

TStat = 
var CurrentDate = Date(2019,9,10) -- for testing
var TicketStatus = 
Switch (
 true(),
 ISBLANK(Tickets[End Date]), 
    Switch (
      true(),
      DATEDIFF(Tickets[Create Date], CurrentDate, MONTH) > 1, "Open Backlog",
      DATEDIFF(Tickets[Create Date], CurrentDate, MONTH) = 1, "Open Backlog New",
      DATEDIFF(Tickets[Create Date], CurrentDate, MONTH) = 0, "Open"
 ),
 NOT ISBLANK(Tickets[End Date]), 
    Switch (
      true(),
      DATEDIFF(Tickets[Create Date], Tickets[End Date], MONTH) >= 1, "Closed Backlog",
      DATEDIFF(Tickets[Create Date], Tickets[End Date], MONTH) = 0, "Closed"
 ) 
)
var Result = TicketStatus 
return 
Result

Hope this helps. 

Jan 

if this is a solution for you, don't forget to mark it as such. thanks

[Anonymous]

Hey @JustJan , thanks for your answer. I just tried it and it didn't work as it was intended. I'm thinking I need three measure, one for the backlog, the other one for the closed backlog and one for closing rates.

I created a measure inspired in your answer but it doesn't display correct values. I'm thinking it's something realted to the relationship I have between the fact table and the calendar table.

I'm adding the Date Hierarchy to the categories field and my measure to the values field in the Excel Pivot Chart. Since only one of the relationships with the calendar dat is active, the one with the StartDate field, I think the EndDate is not being calculated well.

This is the measure I just created, if you have any ideas to activate both of the relationships for this calculation or another way to solve the problem, I would appreciate it. Thanks a lot.

Tickets backlog:=CALCULATE (
    COUNTA ( OtrsData[TicketID] );
    FILTER (
        OtrsData;
        DATEDIFF (
            OtrsData[StartDate];
            IF ( ISBLANK ( OtrsData[EndDate] ); TODAY (); OtrsData[EndDate] );
            MONTH
        ) > 0
    )
)

[JustJan]

Hi @Anonymous ,

Hopefully I got it covered now. The challenge (at least for me) was the generate MONTH values between the create and end date of the ticket. 

The trick was actually to not have a relationship with the date table. 

I used some very simple data. So maybe it does not cover all situtations.

So I assumed that a ticket is only marked as new backlog once. My A1 will only in August 2019 as new. A2 only in September and so on. 

The measures used: 

TicClosedBackLog = 
var CurrentMonth = Month(MIn(Dates[Date]))
var __NextMonth = MOnth(DATEADD('Dates'[Date],1,Month))
var countTickets =
SUMX( 
FILTER (
'Tickets',
Not ( 
'Tickets'[Create Date] > MAX ( 'Dates'[Date] )
|| if ( ISBLANK(Tickets[End Date]), ToDay(), Tickets[End Date]) <MIN('Dates'[Date] )
) 
)
,
SWITCH ( 
TRUE(),
-- ticket created in this month
Month('Tickets'[Create Date]) = CurrentMonth , 0, 
-- Backlog, indicate closing month
month('Tickets'[End Date]) - Month('Tickets'[Create Date]) > 1 && month('Tickets'[End Date]) = CurrentMonth , 1,
0
)
)
var result = countTickets 
return 
result

TicNewOpenBackLog = 
var CurrentMonth = Month(MIn(Dates[Date]))
var countTickets =
 SUMX( 
 FILTER (
 'Tickets',
 Not ( 
 'Tickets'[Create Date] > MAX ( 'Dates'[Date] )
 || if ( ISBLANK(Tickets[End Date]), ToDay(), Tickets[End Date]) <MIN('Dates'[Date] )
 ) 
 )
 ,
 SWITCH ( 
 TRUE(),
 -- ticket created in this month
 Month('Tickets'[Create Date]) = CurrentMonth , 0, 
 -- New Backlog in next month, but closed in one of the later months
 Month('Tickets'[Create Date]) < CurrentMonth && month('Tickets'[End Date]) > CurrentMonth && not (CurrentMonth - month('Tickets'[Create Date])) > 1 , 1,
 -- Backlog in next month, and not yet closed 
 Month('Tickets'[Create Date]) < CurrentMonth && ISBLANK(month('Tickets'[End Date])) && not (CurrentMonth - month('Tickets'[Create Date])) > 1, 1,
 0
 )
 )
 var result = countTickets 
 return 
 result

TicPctClosed = if ( [TicClosedBackLog] > 0 && [TicNewOpenBackLog] > 0 , DIVIDE( [TicClosedBackLog] , [TicNewOpenBackLog]), 0)

Hopefully this makes some sense. You may need to adjust it somewhat to cover the year endings.

Jan 

[Anonymous]

@JustJan Hey Jan, thanks a lot for your answer and your effort putting these measures together. I see this going in the right direction! I will try it out and I will let you know.

I will try to make a new measure out of TicNewOpenBackLog, like a running total of that measure, since I have to show each month what open tickets are being dragged to each month. In the hypotetical data used in your example, for september it should show three tickets instead of one.

I will check and I will let you know. Thanks a lot again.

[Anonymous]

Well, I found this thread here in the community ( Link ) and it helped me tons with the running total backlog and I got those measures working in my report:

Open tickets:=COUNTA(OtrsData[FechaIni])

Solved tickets:=CALCULATE (
    COUNTA ( OtrsData[FechaSol] );
    USERELATIONSHIP ( 'Calendar'[Date]; OtrsData[FechaSol] )
)

Balance:=[Open tickets]-[Solved tickets]

Running Total Backlog:=CALCULATE (
    [Balance];
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[Date] <= EOMONTH ( MAX ( 'Calendar'[Date] ); -1 )
    )
)

Now I'm trying to create a measure for the solved tickets from earlier months, this far I got two, one I made and the other one I adapted from @JustJan measure. The problem is none of them are giving me the desire results.

These are the measures:

Solved tickets from earlier months:=CALCULATE (
    COUNTA ( OtrsData[FechaSol] );
    USERELATIONSHIP ( 'Calendar'[Date]; OtrsData[FechaSol] );
    USERELATIONSHIP ( 'Calendar'[Date]; OtrsData[FechaIni] );
    FILTER ( OtrsData; OtrsData[FechaIni] <= (EOMONTH ( OtrsData[FechaSol]; -1 )) )
)

Solved tickets from earlier months 2:=CALCULATE (
    SUMX (
        FILTER (
            OtrsData;
            NOT (
                OtrsData[FechaIni] > MAX ( 'Calendar'[Date] )
                    || IF ( ISBLANK ( OtrsData[FechaSol] ); TODAY (); OtrsData[FechaSol] )
                        < MIN ( 'Calendar'[Date] )
            )
        );
        SWITCH (
            TRUE ();
            MONTH ( OtrsData[FechaIni] ) = MONTH ( MIN ( 'Calendar'[Date] ) ); 0;
            MONTH ( OtrsData[FechaSol] ) - MONTH ( OtrsData[FechaIni] ) > 1
                && MONTH ( OtrsData[FechaSol] ) = MONTH ( MIN ( 'Calendar'[Date] ) ); 1;
            0
        )
    );
    CROSSFILTER ( 'Calendar'[Date]; OtrsData[FechaIni]; NONE )
)

And this is the chart from the measures I got:

The running total backlog is fine, but I can't seem to get my desired results for the solved tickets from earlier months. This is how it should look:

I hope you can help me out guys because this is getting me crazy. Thanks in advance. I will edit soon and post a file with sample data.

Thanks again for all your help!

Sample Data Here!