Find the counts of missing Accs, counts of added Accs and count of retained Accs

[SandhyaGiriraj]

Table Name: Query1

Source	Rep_date	AccNo	Rev
AB	07/01/2022 12:00:00AM	0011	20$
AB	07/01/2022 12:00:00 AM	1234	50$
AB	08/01/2022 12:00:00 AM	456	100$
AB	09/01/2022 12:00:00AM	1234	200$
AB	09/01/2022 12:00:00 AM	789	90$

From the above table, I have to find Count of accs missing and count of accs added for previous month and current month. 

My current and Previous months date will change for every month. So I expect the DAX to be Dynamic.

For example here the previous month is 08/01/2022 in that I have 1234 account but in current month I don't see that account so 1 account deactivated.

where as a new account is added in current month then I will get 1 account added.

if the acc remains same for both the month then it goes to retained.

[SandhyaGiriraj]

@FreemanZ ,

The CountActive and Count Missing DAX looks the same. How come the Active and Deacts would differ. Could please help

[FreemanZ]

that is your business logic. Retain means an account needs to present in both months, so is INTERSECT. 

[SandhyaGiriraj]

@FreemanZ, I want to show the counts of Deacts in a separate table and with the formula what I have it is not working as expected. Please help

[FreemanZ]

@SandhyaGiriraj what does Deacts mean in your case?

[FreemanZ]

Something like this?

the code for three measures:

CountRetain =

VAR AccListCurrent =  

    VALUES (TableName[AccNO])

VAR AccListLastMonth =

    CALCULATETABLE(

        VALUES(TableName[AccNO]),

        DATEADD(TableName[Date], -1, MONTH)

    )

RETURN

    COUNTROWS(

        INTERSECT(AccListCurrent,  AccListLastMonth)

    )

 

CountNew =

VAR AccListCurrent =  

    VALUES (TableName[AccNO])

VAR AccListLastMonth =

    CALCULATETABLE(

        VALUES(TableName[AccNO]),

        DATEADD(TableName[Date], -1, MONTH)

    )

RETURN

    COUNTROWS(

        EXCEPT(AccListCurrent,  AccListLastMonth)

    )

 

CountMissing =

VAR AccListCurrent =  

    VALUES (TableName[AccNO])

VAR AccListLastMonth =

    CALCULATETABLE(

        VALUES(TableName[AccNO]),

        DATEADD(TableName[Date], -1, MONTH)

    )

RETURN

    COUNTROWS(

        EXCEPT( AccListLastMonth, AccListCurrent)

    )