CODEWITHSUNDEEP
dax
Ricky Nichols
Ricky Nichols

Reputation: 1

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

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?

Upvotes: 0

Views: 187

Answers (1)

msta42a
msta42a

Reputation: 3741

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

Upvotes: 0

Related Questions