Forum Discussion

tsuggs1's avatar
tsuggs1
Helper I
6 years ago
Solved

Using DateDiff to find days between two dates in the same column using another column as a filter.

Hi all,   I am having issues with finding the days between two dates that are in the same column.   Basically the table has a column called 'actualTime' that has a date and another column called ...
Need Help
  • tsuggs1's avatar
    tsuggs1
    6 years ago

    Hi,

     

    Thanks for the response.

     

    I essentially found a solution using the same concept however I did it in a much simpler way using the GUI built into Power BI. I duplicated the table and filtered the original by the code I wanted. Then I filtered the duplicated table to the secondary code I needed. After that I created a relationship between the two tables and was able to pull the dates into my DATEDIFF formula to produce the number of days between. Everything seems to be working great!

     

    Thanks again.

  • tsuggs1's avatar
    tsuggs1
    Helper I
    6 years ago

    Hi Greg,

     

    I read over your article however I don't think what I am trying to accomplish is quite the same or complex. Your formula for Repair Hours = DATEDIFF(Repairs[RepairStarted], Repairs[RepairCompleted],SECOND)/3600 is closer to what I am looking for, but these dates are in separate columns whereas in my table the issue is the two dates are not separated into separate columns, and I don't know how to generate a calculated column that would take the 'actualTime' for a specific code and generate this date for all of the same 'Plan_OID' entries in the table if that makes sense.

     

    Maybe one of the other formulas could be broken down to do that, am I missing something?

     

     

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

      Hi tsuggs1 ,

       

      We can create two spreate table as the slicers and create a measure to meet your requirement:

       

      Code1 = DISTINCT('Table'[code])

       

      Code2 = DISTINCT('Table'[code])

       

      Between Days =
ABS (
    DATEDIFF (
        CALCULATE (
            MAX ( 'Table'[actualTime] ),
            'Table'[code] IN FILTERS ( Code1[code] )
        ),
        CALCULATE (
            MAX ( 'Table'[actualTime] ),
            'Table'[code] IN FILTERS ( Code2[code] )
        ),
        SECOND
    ) / 3600 / 24
)

       

       


      If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


      Best regards,

       

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

      Hi tsuggs1 ,

       

      How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


      Best regards,

       

      • tsuggs1's avatar
        tsuggs1
        Helper I
        6 years ago

        Hi,

         

        Thanks for the response.

         

        I essentially found a solution using the same concept however I did it in a much simpler way using the GUI built into Power BI. I duplicated the table and filtered the original by the code I wanted. Then I filtered the duplicated table to the secondary code I needed. After that I created a relationship between the two tables and was able to pull the dates into my DATEDIFF formula to produce the number of days between. Everything seems to be working great!

         

        Thanks again.