topic Re: Find the counts of missing Accs, counts of added Accs and count of retained Accs in DAX Commands and Tips

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

Anonymous — Mon, 21 Nov 2022 21:57:51 GMT

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.

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

FreemanZ — Tue, 22 Nov 2022 02:09:20 GMT

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)

)

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

Anonymous — Tue, 22 Nov 2022 14:22:16 GMT

FreemanZ ,

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

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

FreemanZ — Tue, 22 Nov 2022 14:56:28 GMT

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

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

Anonymous — Tue, 22 Nov 2022 16:13:10 GMT

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

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

FreemanZ — Tue, 22 Nov 2022 21:33:50 GMT

Anonymous what does Deacts mean in your case?