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

[BraneyBI]

If your purchases are in one table and the Products are in a related Product dimension table, you can simply use a Min or Max.  From there, you can do calculations like days between first and last purchase.

The values of these are easy to see when placed in a table where Product is on rows. 

Earliest Purchase = MIN(Purchases[Purchase date])

Days between Purchases = VALUE([Latest Purchase] - [Earliest Purchase])

If your requirement is different, can you explain more and upload a sample data set. 

[Boycie92]

Hi all,

I appreciate all of your responses

I have to apologies as I haven’t been very clear

All of your solutions show me the last or the first date. What I want is a calculated column or measure that will point out at the row level the first or last dates.

Example:

Product ID           Transaction Date             Identifier

123                         1/1/2017                              First

123                         1/3/2017                              Last

123                         1/2/2017                              null

Sorry again,

Boycie92

[Vvelarde · Accepted solution]

@Boycie92

Hi, Please try with this Calculated Column:

Identifier =
VAR FirstTransactionDate =
    CALCULATE (
        FIRSTDATE ( Table1[Transaction Date] ),
        FILTER ( Table1, Table1[Product ID] = EARLIER ( Table1[Product ID] ) )
    )
VAR LastTransactionDate =
    CALCULATE (
        LASTDATE ( Table1[Transaction Date] ),
        FILTER ( Table1, Table1[Product ID] = EARLIER ( Table1[Product ID] ) )
    )
RETURN
    SWITCH (
        TRUE (),
        Table1[Transaction Date] = FirstTransactionDate, "First",
        Table1[Transaction Date] = LastTransactionDate, "Last",
        BLANK ()
    )

Regards

Victor

Lima - Peru

[drwinny]

I was able to use this on my project, thank you for taking the time to help.

[Boycie92]

Thank you @Vvelarde it works like a charm!

Thanks to all those that answered.

Boycie92

[BraneyBI]

One more clarifier:

In your example, will all three field be located in the same table?

[Boycie92]

Hi @BraneyBI

Yes they will all be within the same table.

Thanks,

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

[vanessafvg]

@Boycie92 

this might work?

datetype=
IF (
transactiondate = FIRSTDATE ( transactiondate ),
"first date",
IF ( transactiondate = LASTDATE ( transactiondate ), "last date", "n/a" )
)

[Boycie92]

Hi @vanessafvg

Thanks for the reply. I have tried it and for some reason all I am getting is "first date" for every row/product?

Any ideas why?

Thanks,

Boycie92

[Boycie92]

Hi,

@vanessafvg

Thanks for the reply. I have tried it and for some reason all I am getting is "first date" for every row/product?

Any ideas why?

Thanks,

Boycie92

[MFelix]

Hi @Boycie92,

If you only want to view the first date and last date the easiste setup in a table would be:

1 - Product description - don't summarize

2 - Transaction Date - Earliest

3 - Transaction Date - Latest

And you get the following result:

+

Regards,

MFelix

[vanessafvg]

@Boycie92

how are you viewing the data?  if you place your data on a matrix

with product, you can create two measures

 firstdate = firstdate(transactiondate)

lastdate = lastdate(transactiondate)

then place those on the matrix with product

[erik_tarnvik]

I assume you have a transaction date in your table? Try these measures:

EarliestTransaction = MIN(Table[TransactionDate])
LatestTransaction = MAX(Table[TransactionDate])

If you create a table visual and drop Table[ProductID] and then the measures into the table you should get the earliest and latest transactions date per product as a result.