Forum Discussion

segmentread01's avatar
segmentread01
Regular Visitor
3 years ago

dax RFM recency add last purchase date to table variable

Hi there, here is the brief introduction for my question: 

I have a main table [user-events] with user id, purchase date, product id etc.. I was trying to build up a RFM dashboard that allows to filter corresponding R, F, M values according to selected date : 

(image of simplified RFM dashboard. With the "problematic" measure lastPurchaseDate )

Here is how I build up the dashboard:

First, I built up an aggregated RFM table with groupby() function on monetary values, it works fine (see #monetary below).

Then, I tried to calculate the lastPurchaseDate and attach the measure to my table variable (monetaryPerMonth), it seems wrong.

So, my questions is, how to add a last purchase date to [R F M aggregate] table, with blank values replaced by [purchase month] ?

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    segmentread01 , In term of new column


    Last purcahse = maxx(filter(Table, [User ID] = earlier([User id]) && [Purchase Date] < earlier([Purchase Date]) ), [Purchase Date])


    Lastest purcahse = maxx(filter(Table, [User ID] = earlier([User id]) ), [Purchase Date])


    Measure

    Lastest purcahse = maxx(filter(allselected(Table), [User ID] = Max([User id]) ), [Purchase Date])