I work at a nonprofit with specific KPIs for donors based on the length of time between their two previous donations. We categorize donors in the following ways:
Within the set of active donors, there are four mutually exclusive groups:
There is a customer (donor) dimension table and a transaction (donations) fact table that are related via a numeric customer id.
Based on this thread I've tried the following as a measure on the Customer (donor) table:
Second Last Transaction Date = VAR LastTransDate = MAX(Transactions[transaction_date]) RETURN CALCULATE( MAX(Transactions[transaction_date]), FILTER(Transactions[transaction_date] < LastTransDate) )
This works as expected as long as no filters are applied at the report, page, or visual level. The next thing I tried was adding ALL(Transactions), which I quickly realized gets all the transactions (including ones not related to the particular row's customer). I also tried FILTER( ALL( Transactions [ customer_id ], Transactions [ customer_id ] = Customers [ customer_id ] ), But that didn't work either.
I think I need to find a way to get all transactions for each donor without any filters applied, but I can't quite figure out how.
Ultimately, I'd like to be able to use these categories (new, retained, reactivated lasped, reactivated lost) as dimensions for other visualizations (e.g. # of reactivated lapsed donors per year for the last 5 years).
Are you saying the formula you have shown here is a calculated column? You should use it as a measure and it should work. I see no issue with other filters being applied.
It is a measure and it does work, but not in the way that I intend. Here is a specific example to illustrate:
We have regional offices who will often filter reports based on the region in which the transaction was processed (financial_region). So let's say a customer has transactions in 2 financial regions as follows:
cusomer_id transaction_date transaction amount financial_region
If someone is looking at a report with only financial_region = CENTRAL selected, then the measure I posted returns nothing because there are no transactions in the CENTRAL region prior to the one on 3/12/2018.
The desired behaviour would be to get the second last transaction date of the all the transactions for a particular customer_id regardless of what filters are applied to a report.
VAR LastTransDate = MAX(Transactions[transaction_date]) RETURN CALCULATE( MAX(Transactions[transaction_date]), FILTER(Transactions[transaction_date] < LastTransDate),ALLEXCEPT(Transactions,Transactions[transaction_date],Transactions[Donor]) )