Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Review Times

Hello, 

 

I need to calculate the difference between 2 date columns to determine how long a review took for each project. I need to do this for each row in the table not the columns as a whole. Does anyone know if and/or how this can be done? 

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    If you select "create measure", then use the formula below:

    Day= DATEDIFF(MAX('Dates'[Complete Final App Received]) , MAX('Dates'[Final App Approved]), Day)

    If you select "create calculated column", use the formula you provided.

     

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hey Anonymous 

     

    Assuming you just need days and not time this is possible, thought depending on what your data looks like it can get complicated.

     

    First suggestion is to see if you can use DATEIFF like in this thread: https://community.powerbi.com/t5/Desktop/Calculate-difference-between-two-date-time-values/td-p/71841

     

    If that does not work then you can try adding an Age column in the query like in this thread: https://community.powerbi.com/t5/Desktop/Difference-between-two-dates-DAX/td-p/157320

     

    The simplest solution that often doesn't work is to subtract the two and multiple by 1. to give you a numerical value for the date as in this thread: https://community.powerbi.com/t5/Desktop/calculate-number-of-days-between-2-dates/td-p/51213

     

    Let me know if none of these work for you.

     

    If this helps please kudo.

    If this solves your problem please accept it as a solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

      My formula is this-

       

      Day= DATEDIFF('Dates'[Complete Final App Received] , 'Dates'[Final App Approved], Day)

       

      I keep get this error-

      "A single value for column 'Complete Final App Received' in table 'Dates' cannot be determined. This can happen when a measure formula refers to a comlun that contains many values without specifying an aggregation such as min, max, count or sum to get a single result."

       

      I just need to know how many days passed between the dates in each of these columns in my table. 

       
       
      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi Anonymous 

        If you select "create measure", then use the formula below:

        Day= DATEDIFF(MAX('Dates'[Complete Final App Received]) , MAX('Dates'[Final App Approved]), Day)

        If you select "create calculated column", use the formula you provided.

         

         

        Best Regards
        Maggie
        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.