employee master and transfer data

^{Hi Lalit_101 ,}

 

^{Please follow these steps:}

^{(1) Create a new Table}

Table = 
SELECTCOLUMNS (
    'Transfer',
    "empcode", [empcode],
    "EffectiveDate", [EffectiveDate],
    "BU",
        IF (
            CALCULATE (
                DISTINCTCOUNT ( 'Transfer'[AttributeTypeDescription] ),
                FILTER (
                    ALLEXCEPT ( 'Transfer', 'Transfer'[empcode] ),
                    [AttributeTypeDescription] = "BU"
                )
            ) > 0,
            IF ( [AttributeTypeDescription] = "BU", [OLDAttributeDesc] ),
            CALCULATE (
                MAX ( 'Live employee'[BU] ),
                FILTER ( 'Live employee', [empcode] = EARLIER ( 'Transfer'[empcode] ) )
            )
        ),
    "Funtion",
        IF (
            CALCULATE (
                DISTINCTCOUNT ( 'Transfer'[AttributeTypeDescription] ),
                FILTER (
                    ALLEXCEPT ( 'Transfer', 'Transfer'[empcode] ),
                    [AttributeTypeDescription] = "Function"
                )
            ) > 0,
            IF ( [AttributeTypeDescription] = "Function", [OLDAttributeDesc] ),
            CALCULATE (
                MAX ( 'Live employee'[function] ),
                FILTER ( 'Live employee', [empcode] = EARLIER ( 'Transfer'[empcode] ) )
            )
        )
)

^{(2) Create a new measure}

headcount = 
CALCULATE (
    COUNT ( 'Live employee'[empcode] ),
    FILTER (
        'Live employee',
        RELATED ( 'Transfer'[EffectiveDate] ) = BLANK ()
            || EOMONTH ( RELATED ( Transfer[EffectiveDate] ), 0 )
                <= EOMONTH ( MIN ( 'calender'[Date] ), 0 )
    )
)
    + CALCULATE (
        COUNT ( 'Table'[empcode] ),
        FILTER (
            'Table',
            EOMONTH ( [EffectiveDate], 0 ) > EOMONTH ( MAX ( 'calender'[Date] ), 0 )
                && [Funtion]
                    IN VALUES ( 'Live employee'[function] )
                        && [BU] IN VALUES ( 'Live employee'[BU] )
        )
    )

^{(3)Final output}

 

 

 

^{Best Regards,}

^{Gallen Luo}

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