Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
@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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |