Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ltilley
Regular Visitor

Second Last Transaction Date without filters

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

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

4538271/21/2018200WEST
4538273/12/2018150CENTRAL
4538274/5/2018100WEST

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.