Forum Discussion

Richard_Thomas's avatar
Richard_Thomas
Regular Visitor
3 years ago
Solved

Calculating Average Days

Hi All, 

 

I am trying to calculate the average number of days between two dates in two different tables. I've used the following formula to create a new column in one of the tables:

 

Days = DATEDIFF(WorksCompleted[DateWorksCompleted], RELATED(WorksRequested[DateSentToContractor]), DAY) 
 
This gives me the average number of days but it is showing this a negative number (e.g. -14 ) whereas I need it to be shown as a postive number. I've tried reversing the formula (e.g. used Days = DATEDIFF(WorksRequested[DateSentToContractor], RELATED(WorksCompleted[DateWorksCompleted]),DAY) but this causes an error to be returned as there is a Many to One relationship here which causes the error. 
 
Is there any way I can change the negative number returned for this average to a postive number?
 
Thanks in anticipation
 
Richard
Need Help
  • JoeBarry's avatar
    JoeBarry
    3 years ago

    Hi Richard

     

    The relationship setup seems ok.

     

    I recreated what you have 

     

    I reworked the measure a little. I had the variables named the same as the Tables. I changed this below

    Days between = 
VAR WorksRequested_ =  Max('WorksRequested'[DateSentToContractor])
VAR WorksCompleted_ = MAX('WorksCompleted'[DateWorksCompleted])

RETURN

DATEDIFF(WorksRequested_, WorksCompleted_, DAY)

     

    I got it to work

     

     

    In the Visual, I added the ID column from the WorksRequested. You can then add the measure

     

    Hope this works for you.

     

    Cheers

    Joe

6 Replies

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    3 years ago

    Hi Richard_Thomas 

     

    Create a measure instead. I will presume that both tables have a shared Dimension table with ProjectID or something similar and a shared date dimension table?

    Days between = 
VAR WorksRequested =  Max('WorksRequetsed'[DateSentToContractor])
VAR WorksCompleted = MAX('WorksCompleted'[DateWorksCompleted])

RETURN

DATEDIFF(WorksRequested, WorksCompleted, DAY)

    Add the ProjectID to a visual and then the measure and you will get the days between

     

    Thanks
    Joe

    • Richard_Thomas's avatar
      Richard_Thomas
      Regular Visitor
      3 years ago

      Hi@JoeBarry

       

      Many thanks for the prompt response and help here, much appreciated. Have tried to run this DAX measure but have come up with the error message The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value

       

      The tables are linked by a common ID but don't have a shared date dimension table. 

       

      Any suggestions?

       

      Kind regards

       

      Richard

       

       

       

      • JoeBarry's avatar
        JoeBarry
        Solution Sage
        3 years ago

        Morning Richard

         

        Is it possible to send me a screenshot of all the Relationships with those two tables? 

         

        Thanks