Forum Discussion

aramirez2's avatar
aramirez2
Icon for Helper I rankHelper I
6 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

5 Replies

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    6 years ago

    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])))
     
     
     
    • aramirez2's avatar
      aramirez2
      Icon for Helper I rankHelper I
      6 years ago

      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 ?

      • Tahreem24's avatar
        Tahreem24
        Icon for Super User rankSuper User
        6 years ago

        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.

         

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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