Forum Discussion
Days between two dates average for a customer orders.
Hello,
Sorry very new to this and trying to help migrate(recreate ssrs reports to PowerBI) - All data is pulled in from the SQL server.
I have a [Customer Name] column and a measure with the total number of orders for each customer.
The table contains two date columns - [Recieved Date] and [Effective Date]
I am looking to create a measure that simply shows the avg days between the Rec Date and Eff Date across all the customers orders and call it Avg. Lag Time.
Customer Name Total Orders Avg Lag Time
Smith & Smith 100 30
Thanks for any help. I have search the forums but am finding similiar request that are doing more than I need.
Hi Sagimore88
please try
Avg Lag Time = AVERAGEX ( TableName, DATEDIFF ( TableName[Recieved Date], TableName[Effective Date], DAY ) )
4 Replies
- tamerj1
Community Champion
Hi Sagimore88
please try
Avg Lag Time = AVERAGEX ( TableName, DATEDIFF ( TableName[Recieved Date], TableName[Effective Date], DAY ) )Thank you!
That works but seems to give me the avg lag time of all orders vs giving me the average for the values in the row.
Customer Orders Avg Lag time
Smith 12 39.4
Johnson 1 39.4
Customer and Order are in the same table the two dates i'm averaging are in a different table.
I feel like the results for avg are not taking into account the data from the other two columns.
Thanks Again, Ill keep trying
- tamerj1
What are the relationships between thses tables? Can you share a screenshot of your data model?
