Find the acc missing and newly added

[SandhyaGiriraj]

Hi  all,

Source	R_Date	Acc
C	2022-Jul	123
B	2022-Aug	321
C	2022-Aug	1234

Using the table I wanna find out, 

Acc missing in August and Accounts newly added in August.

for example, in the table for the source C we have account in july but that account was deactivated in august and there is an new account added in august for source C. 

I have to get the accounts deactivated in a separate table and accounts newly added in separate table comparing the months. 

Please Help.

[SandhyaGiriraj]

Hi @v-jianboli-msft;

Thanks for the solution the thing is I have find the count of accounts missing and added. How do I change the DAX.

and the other thing is 

R_Date	Acc	Source	F_P
07/01/2022 12:00AM	1234	C	1
07/01/2022 12:00AM	1235	C	2
07/01/2022 12:00AM	1233	B	2
08/01/2022 12:00AM	1233	B	2
08/01/2022 12:00AM	1234	C	2
08/01/2022 12:00AM	1235	C	1

Hi @v-jianboli-msft ,

quick question on finding missing and added accounts. 

I have an other column called F_P that says whether the account is active or deactivated or newly added. I want to change the formula accordingly. 

For example, above table has an account 1234 in both july and august. But in July the F_P is 1 which means there is one account. but in august they have 2 so one is added that goes into new activation and 1 is base. 

other example, 1235 in July F_P is 2 which means one account with two different products(phone/tv connection) where as F_P in august is 1 which means the account has deactivated some connection which goes into deact.

Please Help

[v-jianboli-msft]

Hi @SandhyaGiriraj ,

Please try:

First create two table visual.

Then create two measures:

Added = 
VAR _a =
    CALCULATE (
        COUNT ( 'Table'[Acc] ),
        FILTER (
            ALL ( 'Table' ),
            [Acc] = MAX ( 'Table'[Acc] )
                && [R_Date] > EOMONTH ( MAX ( 'Table'[R_Date] ), 0 )
        )
    )
VAR _b =
    EOMONTH ( CALCULATE ( MIN ( 'Table'[R_Date] ), ALL ( 'Table' ) ), 0 )
VAR _c =
    CALCULATE (
        COUNT ( 'Table'[Source ] ),
        FILTER ( ALL ( 'Table' ), [Source ] = MAX ( 'Table'[Source ] ) )
    )
RETURN
    IF ( MAX ( 'Table'[R_Date] ) > _b && ISBLANK ( _a ) && _c > 1, 1, 0 )


Missing = 
VAR _a =
    CALCULATE (
        COUNT ( 'Table'[Acc] ),
        FILTER (
            ALL ( 'Table' ),
            [Acc] = MAX ( 'Table'[Acc] )
                && [R_Date] > EOMONTH ( MAX ( 'Table'[R_Date] ), 0 )
        )
    )
VAR _b =
    EOMONTH ( CALCULATE ( MAX ( 'Table'[R_Date] ), ALL ( 'Table' ) ), -1 )
RETURN
    IF ( MAX ( 'Table'[R_Date] ) <= _b && ISBLANK ( _a ), 1, 0 )

Apply them to the two visual's filter separately:

Final output:

Best Regards,

Jianbo Li

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