Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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
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.