Complex opposite filter doing my head in


  • I have a table of transactions by User ID where the "transaction" can be add, spend, redeem etc. I define "Active" as "spend" in last 60 days.  The measure below works perfectly to calculate the number of distinct Active users at the end of each "Visit month" (I use EOM as Visit month is format 1st of each month).

    =CALCULATE(countrows(distinct(transactions[User ID])),filter(transactions,AND(and(transactions[Date]>EOMONTH(WebStats[Visit Month],0)-60,transactions[Date]<EOMONTH(WebStats[Visit Month],0)+1),transactions[Transaction]="spend")))

    I want to now calculate the number of "inactive" users, but can't take a simple total user- active users approach as going to be used in an even more compelx " reactivated users" calculation.  I cannot for the life of me work out how to filter the table to get the right result.  The problem is you can't just put a NOT around the =spend clause as although this removes all the lines where they have spent it will leave in lines where those active users performed some other action and therefore get caught in the DISTINCT calc.   What I need to do is filter this date window for all transactions where the User has done any action including "spend". I understand the logic, but not how to perform it!

    Any help appreciated. Mike

    2012年4月28日 10:43