Count data point for each month it is overdue

[mmcquil2]

I have a dataset pulled from SAP Maintenance with Basic Finish dates and Required End dates. If the basic finish is after the required end date then that work order is considered overdue. It is also overdue if the work order is still open past the required end date. 

I am looking at all work order data from January 2018 to now and would like to create a trend to show month by month, how many Work Orders were over due. The issue is some work orders are completed way past the required end so they would need to be counted for multiple months. There are also work orders that are completed on time that never should be counted as overdue. 

I figure there is a measure that I could create but not sure what the logic looks like. I had created the following for a different dataset: 

CALCULATE(COUNTROWS('All IWR''s'),FILTER('All IWR''s','All IWR''s'[Open Date]<=MAX(Calendar[Date])&& 'All IWR''s'[Close Date]>=MIN(Calendar[Date])))

however this requires that the open date is always before the close date. That is not necessarily the case with my new dataset.

I have tried looking around for solutions but have found none. Any help would be appreciated.

Thanks,

Michael

[Anonymous]

@mmcquil2 I am trying to achieve the same result.  The error i get is when comparing Required End date in the last part of the filter.  It seems to want an aggregate here.

Below you can see my complete code,

Actions Open and Overdue = 
   var MaxDate = MAX(Date_Calendar_Lookup[DateID])
    var MinDate = MIN(Date_Calendar_Lookup[DateID])
    
    RETURN 
        CALCULATE( 
            COUNTROWS(reducedactions),
            REMOVEFILTERS(Date_Calendar_Lookup),
            reducedactions[due_date]>=MinDate 
            && reducedactions[due_date]<=MaxDate 
            && ( 
                reducedactions[completed_at]>reducedactions[due_date]
                  || ISBLANK(reducedactions[completed_at] ) 
                ) 
            )

the part that DAX does not like syntactically is

Any assistance with this would be appreciated.  

Thanks,

John.

[johnt75]

You could try something like

# Overdue orders =
VAR maxDate =
    MAX ( 'Date'[Date] )
VAR minDate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Required End] >= minDate
            && 'Table'[Required End] <= maxDate
            && (
                'Table'[Basic Finish] > 'Table'[Required End]
                    || ISBLANK ( 'Table'[Basic Finish] )
            )
    )

Make sure that your Date table isn't connected to the work orders table. If it has to be connected you may need to add a REMOVEFILTERS('Date') into the CALCULATE statement

[mmcquil2]

So that did not seem to work. I have the calendar table as a seperate file. I am just getting 1 single result for every month (an image is posted below). I have about 6000 rows of data but have also pasted an example. 

Any edits to the formula that would remedy this?

[johnt75]

Try

# Overdue orders =
VAR maxDate =
    MAX ( 'Date'[Date] )
VAR minDate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        REMOVEFILTERS ( 'Date' ),
        'Table'[Required End] >= minDate
            && 'Table'[Required End] <= maxDate
            && (
                'Table'[Basic Finish] > 'Table'[Required End]
                    || ISBLANK ( 'Table'[Basic Finish] )
            )
    )

[mmcquil2]

I got the same result.

[johnt75]

Are any other filters being applied which could affect it?

You can use Performance Analyzer to grab the DAX code for the visual and have a look at it in DAX Studio. That will show exactly which filters are being applied.

[mmcquil2]

I have no filters applied to the visual or the page.

[johnt75]

What is Complex ? Is that in 'Sheet 2' or another table? If another table, how is that related to 'Sheet 2'?

[mmcquil2]

It is in sheet 2 as seen in the above snapshot.

Thanks,

Michael