Problems filtering correctly....close but no cigar so far!

Question

I have a table "transactions" with columns "user ID", "date" (dd/mm/yy hh:mm), "transaction month" (1st of month and year of date) and "transaction" (type of).  I define a user as "active" if they have executed a transaction of type "spend" in the last 60 days. I want to create a pivot table showing how many unique User IDs were "active" at the end of each month.  The logic is simple, but struggling to get the right answer.

I have created a pivot table with "transaction month" as row labels. I then created a measure

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

This sort of works as correctly counts the unique user IDs with transaction=spend for the month in the  row i.e. the March row shows only a count of March transactions that fit the criteria. If I change the 60 day window to 15 it correctly only counts the last 15 days of March.  I can see my problem is that the "transaction month" from the rows is intially filtering the table so it isn't seeing the Feb transactions when doing the count.  I thought the solution was to enclose the transaction table in the filter with ALL i.e. filter(ALL(transactions), and etc so it took all the transactions and then applied the 2 filters.  However what I get then is the same result in every row which is the sum of the previous values.

Completely out of ideas on how to do this so any help appreicated again.

Mike

Answer 1

The answer is this formula works perfectly as a calculated column within the powerpivot window. Clearly some issue with the context of the month rows when trying to create it as a measure.