Forum Discussion

rohanjha1988's avatar
rohanjha1988
Helper II
6 years ago
Solved

First Sales Value

Hi All, Need one small help :-   I have day wise client wise sales data for last 7 years. I need to calculate the average value of first order of clients. I have calculated the first sales date by ...
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of your Calendar Table.  Write these measures:

    Date of first interaction = Calculate(min(Data[Sales Date]),datesbetween(Calendar[Date],minx(all(Calendar),Calendar[Date]),max(Calendar[Date])))

    Sale on first date = calculate(sum(Data[Total sale]),datesbetween(Calendar[date],[Date of first interaction],[Date of first interaction]))

    To your visual, drag Customer from the Data table and the second measure.

    Hope this helps.

     

AllisonKennedy's avatar
AllisonKennedy
Community Champion
6 years ago
Have you calculated that first sales date as MEASURE or COLUMN? If as column, then you can just take average.

My advice would be to add a calculated column to determine if the order is first order date, then use FILTER function and AVERAGEX:
MEASURE = AVERAGEX(FILTER(sales, Sales[ISFIRSTDATE]="Yes"),SalesAmt)
rohanjha1988's avatar
rohanjha1988
Helper II
6 years ago

I have a master list of all the customers where the total consolidated historical sales is available. In the same table, I have fetched the FirstDate of order per customer. Now i need the first order value. I have attached a picture of the query. 

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago
    We'll need to see the column headings for the orders table to help more. Also, what formula did you use for the First Sales Date and what are the names of your tables? The process will be similar.
    • rohanjha1988's avatar
      rohanjha1988
      Helper II
      6 years ago

      There are 2 tables - Sales Master and Customer Master 

       

      Sales Master:- It has order wise details of customers. I have attached the screenshot. 

       

      Cust Master :- In this all the basic details like Customer Id, name etc is given. For calculating the firstdate i have used following formula :- 

       

      First Sales Date =CALCULATE(FIRSTDATE(SalesMaster[Sales Date (DD-MM-YYYY)]))

       

      Ideally I want to create a measure using this First Sales Date as an input.