Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

@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

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

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

@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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Hi Brad,

 

It's my pleasure.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dedelman_clng
Community Champion
Community Champion

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
Not applicable

5-22-2017 12-29-33 PM.png

 

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

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.