Find the count of missing acc and added acc

[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, 

Count of Acc missing in August and Counts of 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.  Get the counts of deactivated accs and activated.

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

Please Help.

[v-rzhou-msft · Accepted solution]

Hi @SandhyaGiriraj ,

My Sample:

Please try below code to create a measure.

Active = 
CALCULATE(DISTINCTCOUNT('Table'[Source ]),FILTER(ALL('Table'),'Table'[R_Date]<=MAX('Table'[R_Date])))

Deactive = 
VAR _ADD =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Previous",
            CALCULATE (
                MAX ( 'Table'[R_Date] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Source ] = EARLIER ( 'Table'[Source ] )
                        && 'Table'[R_Date] < EARLIER ( 'Table'[R_Date] )
                )
            )
    )
RETURN
    COUNTAX (
        FILTER ( _ADD, [R_Date] = MAX ( 'Table'[R_Date] ) && [Previous] <> BLANK () ),
        [Source ]
    )

Result is as below.

Best Regards,
Rico Zhou

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