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

dedelman_clng's avatar
dedelman_clng
Community Champion
9 years ago

Please let us know what your data model looks like (screen cap would be helpful) and the code you are trying that is failing.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

 

Ultimately, I need to get a count of those HealthRecordDates that are > 60 days from the Date of Service.  That is what I need to get in the end.  I did try to first create a new measure, but found that was not correct and a new calculated column would suffice.

 

And here is the DAX I tried: DaysSinceBMI = DATEDIFF(RELATED(ClientsServed[DoS]),v_MostRecentClientHealthData[HealthRecordDate], DAY)

 

UPDATE:  I changed the relationship from many-to-one to one-to-one and I actually did get something to work.  Now I need to analyze the data and make sure I have what I want

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    9 years ago

    One best practice when dealing with any date fields is to have a calendar table in the model.

     

    Or, if it truly is (and always will be) a 1-to-1 relationship, merge the two tables in Query Editor then you should have no problems.

     

    Actually, just guessing based on what you've shown it would probably be 1-to-many but in the other direction (many health records to one patient).