Practice Changes based on Dates

[rush]

Hi All

I have a table that records a staff's division data. I am trying to create a table out of it with start and end dates along with identifying when a staff has changed their practice e.g DAT to WEB with their corresponding Start and End Dates.

If the staff has changed practices and is still active then it should use an end date of the current day.

Please see the excel file in the link below to see my current and expected data in Power BI.

Practice Changes - Sample Data 

[Anonymous · Accepted solution]

Hi @rush ,

Please refer to my pbix file to see if it helps you.

yea_no =
VAR SHANG =
    CALCULATE (
        MAX ( 'Table'[Practice] ),
        FILTER (
            'Table',
            'Table'[EEID] = EARLIER ( 'Table'[EEID] )
                && 'Table'[Full Name] = EARLIER ( 'Table'[Full Name] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) - 1
        )
    )
VAR _xia =
    CALCULATE (
        MAX ( 'Table'[Practice] ),
        FILTER (
            'Table',
            'Table'[EEID] = EARLIER ( 'Table'[EEID] )
                && 'Table'[Full Name] = EARLIER ( 'Table'[Full Name] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) + 1
        )
    )
VAR _result =
    IF ( ISBLANK ( SHANG ), _xia, SHANG )
RETURN
    IF ( _result <> 'Table'[Practice], "Yes", "No" )

start_ = 'Table'[Job Information - Effective Date]

end_ =
VAR _mindate =
    CALCULATE (
        MAX ( 'Table'[Job Information - Effective Date] ) - 1,
        FILTER (
            ALL ( 'Table' ),
            'Table'[Full Name] = EARLIER ( 'Table'[Full Name] )
                && 'Table'[EEID] = EARLIER ( 'Table'[EEID] )
                && 'Table'[yea_no] = "Yes"
        )
    )
VAR insertmin =
    CALCULATE (
        MIN ( 'Table'[Job Information - Effective Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Full Name] = EARLIER ( 'Table'[Full Name] )
                && 'Table'[EEID] = EARLIER ( 'Table'[EEID] )
                && 'Table'[yea_no] = "Yes"
        )
    )
RETURN
    IF (
        'Table'[Job Information - Effective Date] = insertmin
            && 'Table'[Termination Date] = BLANK (),
        _mindate,
        IF (
            'Table'[Job Information - Effective Date] <> insertmin
                && 'Table'[Termination Date] = BLANK (),
            TODAY (),
            IF (
                'Table'[Job Information - Effective Date] = insertmin
                    && 'Table'[Termination Date] <> BLANK (),
                _mindate,
                IF (
                    'Table'[Job Information - Effective Date] <> insertmin
                        && 'Table'[Termination Date] <> BLANK (),
                    'Table'[Termination Date],
                    BLANK ()
                )
            )
        )
    )

Best Regards

Community Support Team _ Polly

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

[rush]

@Anonymous  Thank you very much. The column to tag if there is a duplicate works well. I managed to filter my table to bring back all staff that has a change in practice.

Thanks once again for all your help. Much appreciated.

[Anonymous]

Hi @rush ,

Please refer to my pbix file to see if it helps you.

Create columns.

Practice Changed =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Practice] ),
        FILTER (
            'Table',
            'Table'[EEID] = EARLIER ( 'Table'[EEID] )
                && 'Table'[Full Name] = EARLIER ( 'Table'[Full Name] )
                && 'Table'[Practice] = EARLIER ( 'Table'[Practice] )
        )
    )
RETURN
    IF ( _count > 1, "No", "Yes" )

start_ = 'Table'[Job Information - Effective Date]

end_ =
VAR _today =
    FORMAT ( TODAY (), "dd/mm/yyyy" )
RETURN
    IF (
        ( 'Table'[Termination Date] ) = BLANK (),
        _today,
        'Table'[Termination Date]
    )

Could you please tell me how to get the date of the marker? The above table does not have these two data and the explanation.

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

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

[rush]

@Anonymous Thank you very much for your effort.

The marked dates in the example of Jo Ko should look at the start date of the next practice and use an end date a day before that as you have it there.

[Anonymous]

Hi @rush ,

Please modify the end_ column.

end_ =
VAR _mindate =
    CALCULATE (
        MAX ( 'Table'[Job Information - Effective Date] ) - 1,
        FILTER (
            ALL ( 'Table' ),
            'Table'[Full Name] = EARLIER ( 'Table'[Full Name] )
                && 'Table'[EEID] = EARLIER ( 'Table'[EEID] )
        )
    )
VAR insertmin =
    CALCULATE (
        MIN ( 'Table'[Job Information - Effective Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Full Name] = EARLIER ( 'Table'[Full Name] )
                && 'Table'[EEID] = EARLIER ( 'Table'[EEID] )
        )
    )
RETURN
    IF (
        'Table'[Job Information - Effective Date] = insertmin
            && 'Table'[Termination Date] = BLANK (),
        _mindate,
        IF (
            'Table'[Job Information - Effective Date] <> insertmin
                && 'Table'[Termination Date] = BLANK (),
            TODAY (),
            IF (
                'Table'[Job Information - Effective Date] = insertmin
                    && 'Table'[Termination Date] <> BLANK (),
                _mindate,
                IF (
                    'Table'[Job Information - Effective Date] <> insertmin
                        && 'Table'[Termination Date] <> BLANK (),
                    'Table'[Termination Date],
                    BLANK ()
                )
            )
        )
    )

Best Regards

Community Support Team _ Polly

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

[rush]

@Anonymous Thank you very much. I found 2 issues.

1. The column to identify if a status has changed for the practice is not working correctly as some staff could have multiple entries and no change in Practice while some staff has one entry and the column is identifying it as a change in practice which is not correct.

2. For the end date, I see it is taking the max end date and not the next corresponding date which it needs to. I have updated the example file to include those cases.

[rush]

@Anonymous Hi , any progress on this. I tried myself but could not get the 

Status Change Flag to calculate correctly.

[amitchandak]

@rush , Check if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[rush]

@amitchandak Thanks but the link did not help me.