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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.