employee master and transfer data

[Lalit_101]

Hi

I have to crack i more logic i have two table like below

1. Live employee Master table

empcode	BU	function
E100	AGRI	HR
E101	ECE	R&D
E102	AGRI	Fin
E103	AGRI	HR

2. Transfer table

empcode	AttributeTypeDescription	NewAttributeDesc	OLDAttributeDesc	EffectiveDate
E100	BU	AGRI	ECE	01-Apr-22
E101	Function	R&D	Fin	5-May-22

i have already created calender table

need to create data like if slicer ( BU is AGRI and Fun is HR) 

Month	headcount
feb-22	1
mar-22	1
apr-22	2
may-22	2
june-22	2
and so on	2

if slicer  (BU is ECE and Fun is R&D)

Month	headcount
feb-22	0
mar-22	0
apr-22	0
may-22	1
june-22	1
and so on	1

pls help

[v-jialluo-msft · Accepted solution]

^{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.}