Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Date comparison does not work properly

Hello,

 

I am new to Power BI and I have an issue with a created column in which two dates are compared, here is the code:

 

Januar 2021 = IF(AND(DATE(2021,1,31)>= RELATED(ProjektZeitraum[KimbleOne__ExpectedStartDate__c]),NOT(ISBLANK(RELATED('Delivery Engagement'[KimbleOne__ExpectedStartDate__c])))),
"ja",
IF(ISBLANK(RELATED('Delivery Engagement'[KimbleOne__ExpectedStartDate__c])),"empty","nein"))
 
What I try to do is to see if January 31st is greater than the date of the related column KimbleOne__ExpectedStartDate__c. If TRUE, it should give out "ja", if FALSE, it should either give out "empty" or "nein", depending if the else condition is true or false. 

This somehow does not work for the February 1st. Since January 31 is not greater (it come before Feb 1st), I should get one of my FALSE-values (either "empty" or "nein"), but I still get the TRUE-value, which does not make any sense. I changed it from January 31st to Jan 15, it still sees the date greater than Feb 1st. Both are of type date. I tried everything I found on the internet, but it just does not work. Once again, I only have this issue with the date February 1st (of the column KimbleOne__ExpectedStartDate__c).
 
I hope you can help me, thank you in advance!
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Anonymous ,

     

    Based on your description, I created a calculated column.

    Januar 2021 = 
var col_kim = RELATED('Table C'[KimbleOne__ExpectedStartDate__c])
return SWITCH(TRUE(),
                ISBLANK(col_kim),"empty",
                DATE(2021,1,31)>=col_kim,"ja",
                "nein")

     

    Please refer to this pbix for details

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Based on your description, I created a calculated column.

    Januar 2021 = 
var col_kim = RELATED('Table C'[KimbleOne__ExpectedStartDate__c])
return SWITCH(TRUE(),
                ISBLANK(col_kim),"empty",
                DATE(2021,1,31)>=col_kim,"ja",
                "nein")

     

    Please refer to this pbix for details

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you very much!

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    How is the setup of your model? You are using the RELATED function but depending on the way the two tables are related you can have different results. This is more particular since your are making the use of two related tables and that can be changing your calculations.

     

    Can you share a sample mockup and how the tables relate?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      The relation is a little bit complicated since the two tables are related through a third table which is in between. Let me explain the relation in a simple way:

       

      I have Table A, in which I have created the column with the formula I posted. Table A is related to Table B (Table B's primary key is used as a foreign key in Table A).

      Table B is related to Table C (Table C's primary key is used as a foreign key in Table B).

       

      What I am doing is using the relationship to create a column in Table A which checks if the date I typed in the formula with the DATE function is greater than the corresponding ExpectedStartDate in Table C. This happens for each row. 

      This is about checking whether the date in the formular is within the project schedule, which is why I am comparing the two dates (Jan 31 > ExpectedStartDate)

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi Anonymous ,

         

        How is the cardinality of the relationship between the tables? One to One - One to Many, filtering for both sides or single side?

         

        Can you make a simple mockup with the ID in each table and the values of the date columns?

         

        Something similar to:

         

        Table A

        ID - Date

        1 - 01/01/2020

        2 - 01/01/2020

         

        Table B

        ID TableA - ID TableC

        1 - 1

        2 - 1

         

        Table C

        ID - Date

        1 - 01/01/2020

        2 - 01/01/2020

         

        Wich