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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.