topic Re: Days between two dates average for a customer orders. in DAX Commands and Tips

Days between two dates average for a customer orders.

Sagimore88 — Thu, 09 Jun 2022 17:02:40 GMT

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.

Re: Days between two dates average for a customer orders.

tamerj1 — Thu, 09 Jun 2022 17:25:15 GMT

Hi Sagimore88

please try

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

Re: Days between two dates average for a customer orders.

Sagimore88 — Tue, 14 Jun 2022 15:21:52 GMT

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

Re: Days between two dates average for a customer orders.

tamerj1 — Tue, 14 Jun 2022 15:31:40 GMT

Sagimore88

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

Re: Days between two dates average for a customer orders.

Sagimore88 — Wed, 15 Jun 2022 16:22:51 GMT

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