Comparison MTD to Previous month based on number of working days with DAX

Question

I'm working with an IT department that wants to see their trend for submitted and closed tickets MTD vs Previous Month, but they want to account for the number of working days so far this month, and compare to the amount of tickets in the same number of working days the previous month. I have a date table with a column marking work days, and another to count the working day in each month.

This is what I had before to find the +/- compared to the previous month, but comparing the current date to the same day last month:

        IF( 
            TOTALMTD(CALCULATE(COUNTA(' Ticket'[Name]),
         'Ticket'[CreatedDateID]<>BLANK(), USERELATIONSHIP('Ticket'[CreatedDateID],'Date'[DateID]) ), 
           'Date'[Date])
         > 
    CALCULATE(COUNTA('Ticket'[Name]),
           AND('Date'[Date]>=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), 'Date'[Date]<=DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY()))),  
         USERELATIONSHIP('Ticket'[CreatedDateID],'Date'[DateID]) )
,
        
        "+" &  TOTALMTD(CALCULATE(COUNTA('Ticket'[Name]), 
         'Ticket'[CreatedDateID]<>BLANK(), USERELATIONSHIP(' Ticket'[CreatedDateID],'Date'[DateID]) ), 'Date'[Date]) 
        -
         CALCULATE(COUNTA(' Ticket'[Name]),
          AND('Date'[Date]>=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), 'Date'[Date]<=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))), 
         USERELATIONSHIP('Ticket'[CreatedDateID],'Date'[DateID]) )
,
        
            TOTALMTD(CALCULATE(COUNTA('LoanForce Ticket'[Name]),
          'LoanForce Ticket'[CreatedDateID]<>BLANK(), USERELATIONSHIP('LoanForce Ticket'[CreatedDateID],'Date'[DateID]) ), 'Date'[Date]) 
    - 
    CALCULATE(COUNTA('LoanForce Ticket'[Name]),
          AND('Date'[Date]>=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), 'Date'[Date]<=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))), 
          USERELATIONSHIP('LoanForce Ticket'[CreatedDateID],'Date'[DateID]) )
        
            )

Sample of Date Table

Can anyone assist in a measure that will take total tickets created this month and compare it to the tickets created previous month in the same amount of work days?

msta42a msta42a · Accepted Answer · 2021-06-05T07:10:11

Where is the problem? As you say, you have "Working Days MTD" then we should use it.

Measure = 
var __CurrentMonthWorkDaysPassed = calculate( MAX(Table[Working Days MTD]), FILTER(ALL(Table[Date]), Table[Date] >= DATE(year(TODAY()),MONTH(TODAY()),1) && Table[Date] <= TODAY()))

var __TicketInCurrentMonth = calculate(count(Table[ColumnToCount]), FILTER(ALL(Table), Table[Working Days MTD] <= __CurrentMonthWorkDaysPassed &&
Table[Date] >= DATE(year(TODAY()),MONTH(TODAY()),1) && Table[Date] <= TODAY()  && Table[Is Working day] = 1 ))

var __TicketInPreviousMonth = calculate(count(Table[ColumnToCount]), FILTER(ALL(Table), Table[Working Days MTD] <= __CurrentMonthWorkDaysPassed &&
Table[Date] >= DATE(year(TODAY()),MONTH(TODAY()) -1 ,1) && Table[Date] < DATE(year(TODAY()),MONTH(TODAY()) ,1)  && Table[Is Working day] = 1 ))

return
__TicketInCurrentMonth / __TicketInPreviousMonth

Comparison MTD to Previous month based on number of working days with DAX

1 Answers