First and Last Date a product was bought

[Boycie92]

Hi,

I am hoping someone could help.

I have a table that contains product ID (numerous different products) and Transaction Date

I want to be able to identify the First Date a product was bought and the Last Date a product was bought.

I thought the best way to achieve this was to identify how many times that product appeared. I have this formula, it counts duplicates of Product ID as a sequence.

Count of Dups = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[ProductID]), EARLIEST(Table[Index]) >= Table[Index])

I tried to sort my table from oldest to newest and the first instance of a ProductID should be labelled as 1. However for some reason that didn’t seem to work and a newer instance was selected.

As for getting the last date a product was sold, I have no idea how to tackle this.

Any help would be appreciated and thanks in advance

Boycie92

[BraneyBI · Accepted solution]

Could put this in one column, but is easier to see in three: 

Earlier has the rank from earliest (1) to latest (N), Latest is reversed. 

Earlier = CALCULATE(COUNTROWS(Purchases),
    FILTER(ALL(Purchases), Purchases[Product] = EARLIER(Purchases[Product])&&Purchases[Purchase date] <= EARLIER(Purchases[Purchase date]))

Later = CALCULATE(COUNTROWS(Purchases),
    FILTER(ALL(Purchases), Purchases[Product] = EARLIER(Purchases[Product])&&Purchases[Purchase date] >= EARLIER(Purchases[Purchase date]))

Status = IF([Earlier]=1, "Earliest",IF([Later]=1,"Latest",BLANK()))