Forum Discussion

JCortez's avatar
JCortez
Frequent Visitor
11 months ago
Solved

Date Calculation

I have these SQL tables below and I am trying to calculate that date difference between these two columns: Client_Assessment.clas_date - Client_Program.start_date I am getting this error below,...
Need Help
  • kushanNa's avatar
    kushanNa
    11 months ago

    Hi JCortez 

     

    See if this Calculated Column DAX Query helps you 

    DateDiff_Days = 
VAR StartDate =
    CALCULATE (
        MIN ( Client_Program[start_date] ),
        FILTER (
            Client_Program,
            Client_Program[client_id] = Client_Assessment[client_id]
                && Client_Program[serv_id] = Client_Assessment[serv_id]
        )
    )
RETURN
IF (
    NOT ISBLANK(StartDate),
    DATEDIFF(StartDate, Client_Assessment[clas_date], DAY),
    BLANK()
)

    I have attached a sample PBIX file. I have tested this code and built it without any relationships, since I’m not sure how you have set them up in yours. Feel free to play around with it and see if it works for you as well. 

     

     

    Date Calculation.pbix260 KB
ToddChitt's avatar
ToddChitt
Super User
11 months ago

Hello JCortez 

It looks like you have to go through table PROGRAM LEVEL to get to the other data field. And there is a many-to-many relationship from that table to Client Assessment. So, from the perspective of Client Program, there could be MANY rows that are related. Power BI doesn't know which row to use.

 

Think of it this way: You ask a parent (that has many children), "How much older are you than your child?" They would have to stop and ask, "Which child?"

 

Check that the relationship is truly Many to Many (and understand the ramification if it is). 

  • JCortez's avatar
    JCortez
    Frequent Visitor
    11 months ago

    I am getting this if I edit that relationship: