Calculating date difference in the same column for different categories

[Anonymous]

Hi,
I have tried looking through the forums, and while there are similar queries there aren't any answers that I have been able to directly apply.  I am trying to work out the days overdue for each service (School/setting, CCP, EP) from the received date - due date.  The other date differences are less of a concern.  

The complexity is they relate to different cases, and different services with different stages in.

I have tried a calculated column, but I am not very experienced with DAX: 

Please could you help with the DAX, or to suggest another method, e.g. how to create a measure for this?

Thanks,

Dan

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please update the formula of calculated column [DateDiff] as below to get the correct datediff, you can find the details in the attachment.

Datediff = 
VAR _DueDate =
    CALCULATE (
        MAX ( 'Prof Reports and Panel - EHCP004'[Date] ),
        FILTER (
            ALLEXCEPT (
                'Prof Reports and Panel - EHCP004',
                'Prof Reports and Panel - EHCP004'[Case Number]
            ),
            'Prof Reports and Panel - EHCP004'[Stage index] = 1
        )
    )
VAR _ReceivedDate =
    CALCULATE (
        MAX ( 'Prof Reports and Panel - EHCP004'[Date] ),
        FILTER (
            ALLEXCEPT (
                'Prof Reports and Panel - EHCP004',
                'Prof Reports and Panel - EHCP004'[Case Number]
            ),
            'Prof Reports and Panel - EHCP004'[Stage index] = 2
        )
    )
RETURN
   DATEDIFF ( _DueDate, _ReceivedDate, DAY )

Best Regards