Forum Discussion

amlopez45's avatar
amlopez45
Frequent Visitor
7 years ago
Solved

Incorrect output for IF function

Hi Everyone, 

 

I am not sure what is going on with the IF function but it is not working as I would expected to. I want to get the number of days between two dates if a certain condition is met. If the condition is not met I want it get me the number of days that have passed. 

 

This is my current formula. 

DateDiff = IF('Table'[Status] = "Closed", DATEDIFF('Table'[Return Date].[Date], 'Table' [Shipping Date].[Date], DAY), DATEDIFF('Table'[Return Date].[Date], TODAY(), DAY))
 
For example, when I add the DateDiff column that contains the formula above it outputs 4 for every row. 
CustomerStatusReturn DateShipping DateDateDiff
AClosed1/28/20192/1/20194
BActive2/4/2019 4
CActive  4
DActive2/4/2019 4

 

How do I get it to output the correct value?

 

Note: When I take out the IF function and seperate the DATEDIFF functions the output is correct. 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI amlopez45,

     

    Can you please share pbix file for test? Your formula works well on my side.

     

    Regards,
    Xiaoxin Sheng

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    hello amlopez45

     

    first things first, try changing your data format (top of modeling tab) to date for your calculated column and see if that fixes your issue

     

    here is a picture of what I did.

    best regards,

    Collin

    • amlopez45's avatar
      amlopez45
      Frequent Visitor
      7 years ago

      Hello Anonymous,

       

      Thank you for the suggestion, the formula is working well now. :smileyhappy:

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        amlopez45

         

        Great to hear, be sure to mark the post as the solution for anyone else that comes along. 

         

        best Regards,

        Collin

         

         

  • amlopez45's avatar
    amlopez45
    Frequent Visitor
    7 years ago

    Hi Everyone, 

     

    I am not sure what is going on with the IF function but it is not working as I would expected to. I want to get the number of days between two dates if a certain condition is met. If the condition is not met I want it get me the number of days that have passed. 

     

    This is my current formula. 

    DateDiff = IF('Table'[Status] = "Closed", DATEDIFF('Table'[Return Date].[Date], 'Table' [Shipping Date].[Date], DAY), DATEDIFF('Table'[Return Date].[Date], TODAY(), DAY))
     
    For example, when I add the DateDiff column that contains the formula above it outputs 4 for every row. 
    CustomerStatusReturn DateShipping DateDateDiff
    AClosed1/28/20192/1/20194
    BActive2/4/2019 4
    CActive  4
    DActive2/4/2019 4

     

    How do I get it to output the correct value?

     

    Note: When I take out the IF function and seperate the DATEDIFF functions the output is correct. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI amlopez45,

     

    Can you please share pbix file for test? Your formula works well on my side.

     

    Regards,
    Xiaoxin Sheng

    • amlopez45's avatar
      amlopez45
      Frequent Visitor
      7 years ago

      Hi Anonymous,

       

      The formula is working well now. I tried Collins method and it seemed to do the job. Not really sure what happened. Thank you for offering to help. :smileyhappy: