I want to Calculate Backlog Tickets for ITSM dashboard

[Kethireddy334]

Hi all,

I want to Caluclate Backlog Tickets based on condition. Backlog Tickets = Created before lastdate of month and Resolved after last date of month or Resolved is blank.

Thanks in Advance

[v-rzhou-msft · Accepted solution]

Hi @Kethireddy334 ,

Here I create a sample to have a test.

Date table:

Date = ADDCOLUMNS( CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))

Measure:

Backlog Tickets = 
VAR _lastdate_of_month = MAX('Date'[Date])
RETURN
CALCULATE(COUNT('Table'[Tickets]),FILTER('Table',[Created Date]<=_lastdate_of_month&&'Table'[Resolved Date] >_lastdate_of_month))

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@Kethireddy334 , I think very similar approch is needed

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or check files attached after signature