Forum Discussion

rush's avatar
rush
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...
  • Anonymous's avatar
    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.

Anonymous's avatar
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.

rush's avatar
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's avatar
    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.

    • rush's avatar
      rush
      Helper V
      4 years ago

      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's avatar
        rush
        Helper V
        4 years ago

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

        Status Change Flag to calculate correctly.