Nr. of open overdue tickets by date

[IvanS]

Hi all,

I have standard table that consists of tasks with columns below:

1. Task ID

2. Status

3. Create date

4. Due date

5. Close date

Based on other user's topic here, I have manage to create mix of measures that are calculating Nr. of Open tickets by date (see below measures). For this I needed to create copy of Tasks KPI table (1st is connected to date table by Close date, and 2nd is connected by Create date).

Measure_ClosedCount = IF(ISBLANK(COUNT('cdata_FACT_Tasks KPI'[ClosedTime])), 0, COUNT('cdata_FACT_Tasks KPI'[ClosedTime]))

Measure_OpenedCount = IF(ISBLANK(COUNT('cdata_FACT_Tasks KPI - Copy'[Id])), 0, COUNT('cdata_FACT_Tasks KPI - Copy'[Id]))

Measure_ClosedSUM = SUMX('Date table', [Measure_ClosedCount])

Measure_OpenedSum = SUMX('Date table',[Measure_OpenedCount])

Measure_PendingCount = [Measure_OpenedSum] - [Measure_ClosedSUM]

Measure_OpenTasksHistory = 
CALCULATE (
    SUMX ( 'Date table', [Measure_PendingCount] ),
    FILTER ( ALL ( 'Date table'[Date] ), 'Date table'[Date] <= MAX ( 'Date table'[Date] ) )
)

Now, I would like to calculate the Nr. of Open Overdue tickets by date and exclude tickets in status "Canceled".

Open Overdue means that ticket was not closed (close time is blank) or was closed after due date.

Perhaps, we can adjust the existing measures but I am not that profficient in DAX so any help is much appreciated!

Thank you

Ivan

[Greg_Deckler]

@IvanS See if this helps: Open Tickets - Microsoft Power BI Community