cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Resolver I

## First and Last Date a product was bought

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

2 ACCEPTED SOLUTIONS
Kudo Commander

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

Community Champion

@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

Lima - Peru
14 REPLIES 14
Kudo Commander

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.

Resolver I

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

Community Champion

@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

Lima - Peru
Helper I

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

Resolver I

Thank you @Vvelarde it works like a charm!

Thanks to all those that answered.

Boycie92

Kudo Commander

One more clarifier:

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

Resolver I

Yes they will all be within the same table.

Thanks,

Boycie92

Kudo Commander

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

Super User

this might work?

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

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Resolver I

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

Resolver I

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

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

@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

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Solution Specialist

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.