Get last date for each ID

[jakobjensen]

Say I have the following:

#[transactions]

id  date
--+-----
1   2021-01-01
1   2021-01-05
2   2021-01-01
2   2021-02-03

and I want to get the day since last purchase. If I create a measure like this

Now()-date

I get it for all transactions but I just want it for the last transaction for each ID.

I could do something like    "MIN(Now()-date)"  but I don't know how to calculate it within a "group" i.e within each "id"

[anat]

load id,min(date) resident table group by id;

you will get min date for each id

then u can do the calculation on top..

[rubenmarin]

Hi, you can use an expression like:

if(date=Max(TOTAL <id> date),date-Max(TOTAL <id> date, 2))

or "if(date=Max({1} TOTAL <id> date),date-Max({1} TOTAL <id> date, 2))" to ignore date selections