Calculating Datediff between status changes

[deanbland]

Hi, I have a somewhat complicated request... 

I export a dataset each week that shows whether employees have clients or not (Benched = no client, Not Benched = has a client). 

The state of utilisation (Benched/Not Benched) changes and I am wanting to assess the time difference between these changes. 

I am trying to find a way to create a calculated column that assesses the status of the 'Benched?' column. When a new report is uploaded, Power BI assesses the status for each employee, and if there is a status change, assigns the date difference between the two reports in the calculated column. 

From the example, you can see on the example data below that Employee ID 2's status changed on 08/01/2021, and so when the next report was uploaded Power BI did a comparison to the previous report and said that the employee has been Benched for 7 days. When the report for 22/01/2021 was uploaded, Power BI did a similar thing, but counted the date diff from the date that the status changed (08/01/2021), and not from the previous week's report (so instead of saying they had been benched for 7 days, it says that have been benched for 14 days). This analysis should carry on until the status changes back to 'Not Benched'. 

Hopefully, this all makes sense? Some example data is in the table below! 

Benched?	Employee ID	Date
Not Benched	1	01/01/2021
Not Benched	2	01/01/2021
Benched	3	01/01/2021
Not Benched	1	08/01/2021
Benched	2	08/01/2021
Benched	3	08/01/2021
Not Benched	1	15/01/2021
Benched	2	15/01/2021
Benched	3	15/01/2021

[Vera_33 · Accepted solution]

@deanbland 

You want a DAX calculated column or DAX measure or M? Here is a DAX column

Column =
VAR CurID = 'Table'[Employee ID]
VAR CurBenched = 'Table'[Benched?]
VAR CurDate = 'Table'[Date]
VAR MaxDate =
    MAXX (
        FILTER (
            'Table',
            'Table'[Employee ID] = CurID
                && 'Table'[Date] < CurDate
                && 'Table'[Benched?] = "Not Benched"
        ),
        'Table'[Date]
    )
VAR MinDate =
    IF (
        MaxDate = BLANK (),
        MINX ( FILTER ( 'Table', 'Table'[Employee ID] = CurID ), 'Table'[Date] ),
        MINX (
            FILTER ( 'Table', 'Table'[Employee ID] = CurID && 'Table'[Date] > MaxDate ),
            'Table'[Date]
        )
    )
RETURN
    IF ( CurBenched = "Not Benched", BLANK (), DATEDIFF ( MinDate, CurDate, DAY ) )

[Anonymous · Accepted solution]

Hi @deanbland ,

You can also create a measure as below base on @Vera_33 ' provided calculated column:

Measurea =
VAR _curemp =   SELECTEDVALUE ( 'Table'[Employee ID] )
VAR _curdate =  SELECTEDVALUE ( 'Table'[Date] )
VAR _nbenchdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Employee ID] = _curemp
                && 'Table'[Date] < _curdate
                && 'Table'[Benched?] = "Not Benched"
        )
    )
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Employee ID] = _curemp )
    )
VAR _mindate2 =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Employee ID] = _curemp
                && 'Table'[Date] > _nbenchdate
        )
    )
RETURN
    IF (
        ISBLANK ( _nbenchdate ),
        DATEDIFF ( _mindate, _curdate, DAY ),
        DATEDIFF ( _mindate2, _curdate, DAY )
    )

If the above one is not working in your scenario, please provide the correct result with some example and calculation logic. Thank you.

Best Regards

[Vera_33 · Accepted solution]

@deanbland 

You want a DAX calculated column or DAX measure or M? Here is a DAX column

Column =
VAR CurID = 'Table'[Employee ID]
VAR CurBenched = 'Table'[Benched?]
VAR CurDate = 'Table'[Date]
VAR MaxDate =
    MAXX (
        FILTER (
            'Table',
            'Table'[Employee ID] = CurID
                && 'Table'[Date] < CurDate
                && 'Table'[Benched?] = "Not Benched"
        ),
        'Table'[Date]
    )
VAR MinDate =
    IF (
        MaxDate = BLANK (),
        MINX ( FILTER ( 'Table', 'Table'[Employee ID] = CurID ), 'Table'[Date] ),
        MINX (
            FILTER ( 'Table', 'Table'[Employee ID] = CurID && 'Table'[Date] > MaxDate ),
            'Table'[Date]
        )
    )
RETURN
    IF ( CurBenched = "Not Benched", BLANK (), DATEDIFF ( MinDate, CurDate, DAY ) )