Finding Second Last Donation Date

DAX DAX Calculations
1

Hi

I’m trying to source the last donation date and second last donation date from the Donations (sales) table to the Pledges table (which has unique Customer IDs) using calculated columns.

I managed to source the last donation date using the formula of:

Last Donation Date = MAXX( FILTER( ALL( Donations ), Donations[Customer ID] = Pledges[Customer ID] ), Donations[Donation Date] )

However I’m struggling to create the column with the second last donation date.

Any suggestions would be much appreciated.

Thanks

1 Like
2

Try something like this.

NewMeasure =
MINX(
    TOPN(
        2,
        FILTER(
            ALL( Donations ),
            Donations[Customer ID] = Pledges[Customer ID]
        ),
        Donations[Donation Date]
    ),
    Donations[Donation Date]
)
2 Likes
3

@KieftyKids,

I would do this in Power Query, which makes it pretty straightforward,. Using the Practice Dataset External Tool, I created a customer sales table to mimic your donors/pledges structure. Here’s the process:

  1. Group on customer and all rows
    2 Add a count of sales (donations) by customer using Table.AddIndex
  2. Expand and regroup on customer
  3. Calculate Max count of sales for each customer using the UI
  4. Create a custom column (I called it Sales Note) with the following code:
    image

Here’s the result:

image

I hope this is helpful. Full solution file attached below.

  • Brian

Last and Second to Last Sale.pbix (157.9 KB)

2 Likes
4

Hi Melissa & Brian

Thank you for your prompt replies.

Melissa, I tried your code and it worked - although I’m not sure why yet.

Brian, I like the idea of sorting out the last and 2nd last sales using Power Query and will attempt your solution once I figure out how to use the Practice Dataset.

Again, thanks.

5

Hi @KieftyKids , did the response provided by @BrianJ and @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!