Helper V

4 years ago

Solved

Practice Changes based on Dates

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

4 years ago

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.

Practice Changes based on Dates (2).pbix149 KB

9 Replies

amitchandak
Super User
4 years ago
rush , Check if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
- rush
  Helper V
  4 years ago
  amitchandak Thanks but the link did not help me.

Anonymous

Not applicable

4 years ago

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.

Practice Changes based on Dates.pbix106 KB

rush

Helper V

4 years ago

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

Not applicable

4 years ago

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.

Practice Changes based on Dates.pbix144 KB

Anonymous

Not applicable

4 years ago

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.

Practice Changes based on Dates (2).pbix149 KB

rush
Helper V
4 years ago
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.

Forum Discussion

Practice Changes based on Dates

9 Replies

Related content

Best Practices: Connecting Power BI to Feishu (Lark) Base (Bitable)?

credential management best practices?

Best Practice Analyzer

Git Integration Best Practices

Copilot - theory versus practice