Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Minimum Purchase Date for a Customer

Hi,

 

I am trying to find the First Purchase Date for the customers.

So in the screenshot below, for this customer First Purchase Date is 4th Sep, but when I add Month Year to the table, the First Purchase Date gets updated to the Min(PurchaseDate) for that Month.  I am trying to find the Transaction Frequency of the customer and this messes up my calculation. Could someone help me with fixing this ?

 

 

 

Thank you!

 

 

Need Help
  • MohammadLoran25's avatar
    MohammadLoran25
    3 years ago

    Hi,

    Create a calculated column in your Customer Dimension as below:

     

    CALCULATE (
        MIN ( 'Date Table'[DateColumn] ),
        RELATEDTABLE ( 'Pucrchase Fact Table' )
    )

    Then your problem would be solved.

     

    Best Regards.

3 Replies

  • MohammadLoran25's avatar
    MohammadLoran25
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi,

    Create a calculated column in your Customer Dimension as below:

     

    CALCULATE (
        MIN ( 'Date Table'[DateColumn] ),
        RELATEDTABLE ( 'Pucrchase Fact Table' )
    )

    Then your problem would be solved.

     

    Best Regards.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you! They all are in a combined table. Should I create a date table which will make it easier ?

    • MohammadLoran25's avatar
      MohammadLoran25
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Yes it is better to make a separate dimension for that.

      But If you don't want to create a dimension you can create a measure as below just based on your combined table:

       

      VAR _CUSTOMER =
          SELECTEDVALUE ( CustomerPurchaseFactTable[CustomerID] )
      RETURN
          CALCULATE (
              MIN ( CustomerPurchaseFactTable[MonthYear] ),
              FILTER (
                  ALL ( CustomerPurchaseFactTable ),
                  CustomerPurchaseFactTable[CustomerId] = _CUSTOMER
              )
          )

       

      Regards,

      Loran