Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Find the acc missing and newly added

Hi  all,

 

Source R_DateAcc
C2022-Jul123
B2022-Aug 321
C2022-Aug1234

 

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.

 

Need Help

2 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi Anonymous ,

     

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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_DateAccSourceF_P
    07/01/2022 12:00AM1234C1
    07/01/2022 12:00AM1235C2
    07/01/2022 12:00AM1233B2
    08/01/2022 12:00AM1233B2
    08/01/2022 12:00AM1234C2
    08/01/2022 12:00AM1235C1

     

    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