Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
deanbland
Helper III
Helper III

Calculating Datediff between status changes

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! 

 

deanbland_0-1617008293423.png

Benched?Employee IDDate
Not Benched101/01/2021
Not Benched201/01/2021
Benched

3

01/01/2021
Not Benched108/01/2021
Benched208/01/2021
Benched308/01/2021
Not Benched115/01/2021
Benched215/01/2021
Benched315/01/2021

 

2 ACCEPTED SOLUTIONS
Vera_33
Resident Rockstar
Resident Rockstar

@deanbland 

 

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

Vera_33_0-1617020212146.png

 

 

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 ) )

 

View solution in original post

v-yiruan-msft
Community Support
Community Support

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 )
    )

 

yingyinr_0-1617181800510.png

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

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

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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 )
    )

 

yingyinr_0-1617181800510.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Vera_33
Resident Rockstar
Resident Rockstar

@deanbland 

 

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

Vera_33_0-1617020212146.png

 

 

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 ) )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.