Forum Discussion

Barry_Sophus's avatar
Barry_Sophus
Frequent Visitor
2 years ago

Date Analysis

Hi,   I have the following situation that I need help with. The objective is to compare Platinum with Silver Plan Dates. For each GroupMemberID of the same value in the Platinum plan, identify the ...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Barry_Sophus ,

You can create a calculated column as below to get it, please find the details in the attachment.

Column = 
VAR _silverdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[GroupId] = EARLIER ( 'Table'[GroupId] )
                && 'Table'[GroupMemberId] = EARLIER ( 'Table'[GroupMemberId] )
                && 'Table'[Employee Coverage Plan] = "Silver"
        )
    )
VAR _ptdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[GroupId] = EARLIER ( 'Table'[GroupId] )
                && 'Table'[GroupMemberId] = EARLIER ( 'Table'[GroupMemberId] )
                && 'Table'[Employee Coverage Plan] = "Platinum"
                && 'Table'[Date] <= _silverdate
        )
    )
RETURN
    IF (
        ( 'Table'[Employee Coverage Plan] = "Silver"
            && 'Table'[Date] = _silverdate )
            || ( 'Table'[Employee Coverage Plan] = "Platinum"
            && 'Table'[Date] = _ptdate ),
        "X"
    )

Best Regards

Barry_Sophus's avatar
Barry_Sophus
Frequent Visitor
2 years ago

It appears that the filtering is brining up all dates where the silver plan is on or after the platinum date. In another words, I need 1 from platinum and 1 from silver. After that iterate through the remaining lines in the group.