Issue using DateDiff for New column

[Anonymous]

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 table, one or the other cannot see that date value.  I get this error - The column 'ClientsServed[DoS]' either doesn't exist or doesn't have a relationship to any table available in the current context.  Except that the column DOES exist.

Can anyone shed any light on this?  I have tried also using the RELATED command and it is not working either.

Thanks

[v-jiascu-msft · Accepted solution]

@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]

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]

Hi Brad,

It's my pleasure.

Best Regards!

Dale

[dedelman_clng]

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]

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]

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).