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, I am not sure if I am creating the new column in the correct table or why this error is coming up. Any assistance would be greatly appreciated.

 

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. 

     

     

7 Replies

  • kushanNa's avatar
    kushanNa
    Super User
    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. 

     

     

  • MasonMA's avatar
    MasonMA
    Super User
    11 months ago

    Hi JCortez 

     

    Would you want to optimize your model and avoid many-to-many relationship so that you have Client_Program link directly to Client_Assessment through a one-to-many? 

     

    Then you can use:

    DateDiff =
    DATEDIFF (
    Client_Assessment[clas_date],
    RELATED ( Client_Program[start_date] ),
    DAY
    )

     

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

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    11 months ago

    Hi JCortez,

    Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to kushanNa, ToddChitt, MasonMA, for those inputs on this thread.

    Has your issue is resolved? As provided solution by community members kushanNa, MasonMA, ToddChitt,

    addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Community Forum.

    • v-kpoloju-msft's avatar
      v-kpoloju-msft
      Community Support
      11 months ago

      Hi JCortez,

      Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

      Thank you.

      • v-kpoloju-msft's avatar
        v-kpoloju-msft
        Community Support
        10 months ago

        Hi JCortez,

        Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

        Thank you.