Forum Discussion

Sagimore88's avatar
Sagimore88
Frequent Visitor
4 years ago
Solved

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 tot...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Sagimore88 

    please try

    Avg Lag Time =
AVERAGEX (
    TableName,
    DATEDIFF ( TableName[Recieved Date], TableName[Effective Date], DAY )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Sagimore88 

please try

Avg Lag Time =
AVERAGEX (
    TableName,
    DATEDIFF ( TableName[Recieved Date], TableName[Effective Date], DAY )
)
  • Sagimore88's avatar
    Sagimore88
    Frequent Visitor
    4 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Sagimore88 

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

      • Sagimore88's avatar
        Sagimore88
        Frequent Visitor
        4 years ago

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