Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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()))
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
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
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
I was able to use this on my project, thank you for taking the time to help.
One more clarifier:
In your example, will all three field be located in the same table?
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()))
this might work?
datetype=
IF (
transactiondate = FIRSTDATE ( transactiondate ),
"first date",
IF ( transactiondate = LASTDATE ( transactiondate ), "last date", "n/a" )
)
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,
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 @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
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
Proud to be a Super User!
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |