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

Be 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

Reply
aramirez2
Helper I
Helper I

First and Last Channel purchase

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.

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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.

1.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

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.

1.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Tahreem24
Super User
Super User

@aramirez2 ,

Try this below DAX for first and Last Channel. 

First Channel = CALCULATE(MAX(TAble[Channel]),FILTER(ALL(DateTable),DateTable[Date]=MIN(TDateTable[Date])))
Last Channel = CALCULATE(MAX(TAble[Channel]),FILTER(ALL(DateTable),DateTable[Date]=MAX(TDateTable[Date])))
Capture.PNG
 
 
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Thanks @Tahreem24  for your answer but your measure formula does not give any result:

 

channel.PNG

 

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.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.