Days between two dates average for a customer orders.

[Sagimore88]

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.

[tamerj1 · Accepted solution]

Hi @Sagimore88 

please try

Avg Lag Time =
AVERAGEX (
    TableName,
    DATEDIFF ( TableName[Recieved Date], TableName[Effective Date], DAY )
)

[Sagimore88]

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]

@Sagimore88 

What are the relationships between thses tables? Can you share a screenshot of your data model?

[Sagimore88]

Thanks that question made me look- I had to change the cross filter on the tables in modeling to 'Both' -