Forum Discussion

ConorHG's avatar
ConorHG
Helper I
8 years ago
Solved

Date Relationship

Hi, 

 

I am struggling with doing a date relationship. 

I have created a calendar table and added several columns. 

 

In both data view and query editor, I have put the dates I am doing the relationship on in date datatype and I can do the relationship.

But I then receive the below error after the relationship. It seems that after doing the join the date field in the facts column is no longer read as a date? 

 

 

This calc works perfectly before I do the relationship.

 

 

Any help is appreciated. 


Thanks, 
Conor.

Need Help
  • FarhanAhmed1984's avatar
    FarhanAhmed1984
    8 years ago

    I think since you are creating relationship of disdate and date table using date column that's why you are limiting the capabilities of date data. Therefore if you need to have all the capabilities for date values (hierarchy) then you should create a DateKey (Integer Value) on both tables and then join it on that. After that you will see all the hierarchy for your date values.

8 Replies

  • FarhanAhmed1984's avatar
    FarhanAhmed1984
    Resolver III
    8 years ago

    Looks like disdate is not a Date Column.

     

    - Click on field.

    - Click on Modelling tab

    - See if data type is Date , change it to date if it is not.

    .

     


    Thanks

     

      • FarhanAhmed1984's avatar
        FarhanAhmed1984
        Resolver III
        8 years ago

        Try this.

         

        No Bed Days = DateDiff(FIRSTDATE('Hipe Data'[AdmDate]),FIRSTDATE('Hipe Data'[DisDate]),DAY)

         

        DateDiff takes Scalar as Input, so you need to have 1 value return for each input.


        You may use MAX() or MIN() as well instead of FIRSTDATE