March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi.
I am trying to find out the First and Last Channel Purchase per CustomerID.
My Sales table has the following structure: Date - CustomerID - Channel - ProductID - Sales.
07/09/2020 | 2wa | Web | af22 | 8 |
07/09/2020 | 7j3 | Web | 22dd | 2 |
06/09/2020 | 2wa | Amazon | 3dd | 5 |
06/09/2020 | 7j3 | Alliexpress | ddd2 | 4 |
05/09/2020 | 2wa | Alliexpress | ss23 | 4 |
Sales Table is linked to my calendar but I am not be able to create a measure which shows up First Purchase Channel and Last Purchase Channel in a table with all my CustomerIds.
May anyone help me please?
Thanks in advance.
Solved! Go to Solution.
Hi @aramirez2 ,
Check the measures below.
first =
var mindate = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[CustomerID]))
return
CALCULATE(MAX('Table'[Channel]),FILTER(ALLEXCEPT('Table','Table'[CustomerID]),'Table'[Date]=mindate))
last =
var last_date = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[CustomerID]))
return
CALCULATE(MAX('Table'[Channel]),FILTER(ALLEXCEPT('Table','Table'[CustomerID]),'Table'[Date]=last_date))
Result would be shown as below.
Best Regards,
Jay
Hi @aramirez2 ,
Check the measures below.
first =
var mindate = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[CustomerID]))
return
CALCULATE(MAX('Table'[Channel]),FILTER(ALLEXCEPT('Table','Table'[CustomerID]),'Table'[Date]=mindate))
last =
var last_date = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[CustomerID]))
return
CALCULATE(MAX('Table'[Channel]),FILTER(ALLEXCEPT('Table','Table'[CustomerID]),'Table'[Date]=last_date))
Result would be shown as below.
Best Regards,
Jay
@aramirez2 , Try like
firstnonblankvalue(Date, sum(Table[sales]))
Lastnonblankvalue(Date, sum(Table[sales]))
They will work for the row context. With the customer in visual, they will give at the customer level, With Customer product, they will give at the customer product level
Try , customer level forced
calculate(firstnonblankvalue(Date, sum(Table[sales])), allexcept(Table, Table[Customer]))
calculate(lasttnonblankvalue(Date, sum(Table[sales])), allexcept(Table, Table[Customer]))
Try this below DAX for first and Last Channel.
Thanks @Tahreem24 for your answer but your measure formula does not give any result:
My Sales tabe is "pedidos" and my Calendar is "Calendario_pedido". Are your measure formulas working if table contained CustomerId and First and Last Purchase formulas only ?
I've created calculate column for that.
As you can see I've attached screen shot with all the required columns and it's working fine.
One thing to focus is make sure you have proper relationship between calendar and pedidos table.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
134 | |
90 | |
90 | |
66 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
70 | |
68 |