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
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.