Forum Discussion
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 FIRSTDATE but totally stuck on how to proceed further.
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.
8 Replies
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.
Many thanks. This formula works. But there is one issue. The grand total is not correct?
Whom are you replying to? If it's me, then share the link from where i can download your Excel file.
- 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)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.
- 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 , 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
