Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Boycie92
Resolver I
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

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

View solution in original post

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

Result.png

 

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

14 REPLIES 14
BraneyBI
Kudo Commander
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. 

 

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

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

Result.png

 

Regards

 

Victor

Lima - Peru




Lima - Peru

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

Thank you @Vvelarde it works like a charm!

 

Thanks to all those that answered.

 

Boycie92

One more clarifier:

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

 

 

Hi @BraneyBI

 

Yes they will all be within the same table.

 

Thanks,

 

Boycie92

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

@Boycie92 

 

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!




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

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
Super User
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:

 

Max_min.png+

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



vanessafvg
Super User
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!




erik_tarnvik
Solution Specialist
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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors