First Sales Value

[rohanjha1988]

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 FIRSTDATE but totally stuck on how to proceed further. 

[Ashish_Mathur · Accepted solution]

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.

[rohanjha1988]

Many thanks. This formula works. But there is one issue. The grand total is not correct?

[Ashish_Mathur]

Whom are you replying to?  If it's me, then share the link from where i can download your Excel file.

[amitchandak]

@rohanjha1988 , if you have incremental order no

First Order Value = AverageX(Summarize(table[client],"_1", FIRSTNONBLANKVALUE(Table[Order No],sum(Table[Sales]))),[_1])
Last Order Value = AverageX(Summarize(table[client],"_1", LASTNONBLANKVALUE(Table[Order No],sum(Table[Sales]))),[_1])

I did not saw the last update. But you may use date, in place of order no if needed

[AllisonKennedy]

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]

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]

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]

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.