Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Issue using DateDiff for New column

I have two data tables that are related using ClientID.  Each table has two different dates that I need to get the Number of Days between each date, however when I go to build a new column in either ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Anonymous

     

    Hi,

     

    It seems that you made a wrong relationship. One client has a DateOfService and a HealthRecordDate. We should create relationship using ClientId. If you could establish a 1:1 relationship with ClientId, you could use this formula in a measure.

    DaysSinceBMI =

    DATEDIFF (

        MIN ( ClientsServed[DoS] ),

        MIN ( v_MostRecentClientHealthData[HealthRecordDate] ),

        DAY

    )

     

    Don’t worry about MIN. When you create a report like this, there will be only one date each. So MIN() is itself. Please have a try.

     ClientId   DaysSinceBMI 

     A              90


          

    Best Regards!

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Anonymous

 

Hi,

 

It seems that you made a wrong relationship. One client has a DateOfService and a HealthRecordDate. We should create relationship using ClientId. If you could establish a 1:1 relationship with ClientId, you could use this formula in a measure.

DaysSinceBMI =

DATEDIFF (

    MIN ( ClientsServed[DoS] ),

    MIN ( v_MostRecentClientHealthData[HealthRecordDate] ),

    DAY

)

 

Don’t worry about MIN. When you create a report like this, there will be only one date each. So MIN() is itself. Please have a try.

 ClientId   DaysSinceBMI 

 A              90


  

Best Regards!

Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    I had to jumble up the syntax some, but this works and I thank you for your guidance:

     

    DaysSinceBMI =
    IF(

         ClientsServed[LastHealthRecordedDate]<=ClientsServed[DateOfService]

         ,DATEDIFF (( ClientsServed[LastHealthRecordedDate] ),( ClientsServed[DateOfService] ),DAY)

         ,-1
    )

     

    the reason I am doing an "IF" statement is we have some bad data where the health record data is past the service date and it is erring on the fact that the last date cannot be greater than the start date (garbage data), so if that is the case then just use -1.

     

    Thanks again,

     

    Brad

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      Hi Brad,

       

      It's my pleasure.

       

      Best Regards!

      Dale