Second Last Transaction Date without filters

[ltilley]

Hi,

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:

1. Active donors: last transaction date is within 18 months of the last day in the reporting period
2. Lapsed donors: last transaction date is 18-36 months prior to the last day in the reporting period
3. Lost donors: last transaction date > 36 months prior to the last day in the reporting period

Within the set of active donors, there are four mutually exclusive groups:

1. New donor: undefined/infinite amount of time between the last two gifts (transactions)
2. Retained donor: < 18 months between last two transactions
3. Reactivated from lapsed: 18 - 36 months between last two transactions
4. Reactivated from lost: >= 36 months between last two transactions.

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).

[Ashish_Mathur]

Hi,

Try this

VAR LastTransDate = MAX(Transactions[transaction_date]) RETURN CALCULATE( MAX(Transactions[transaction_date]), FILTER(Transactions[transaction_date] < LastTransDate),ALLEXCEPT(Transactions,Transactions[transaction_date],Transactions[Donor]) )

[ltilley]

I tried that and based on the documentation available for ALLEXCEPT, it seems like that should work, but when I tried it on my data, it didn't work.

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[MattAllington]

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. 

[ltilley]

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

453827	1/21/2018	200	WEST
453827	3/12/2018	150	CENTRAL
453827	4/5/2018	100	WEST

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.