Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DATEDIFF within IF

Please could someone tell me why this doesn't work?

 

=if([status]="Closed",
DATEDIFF([date_entered],[date_closed],DAY),
"Not Closed"
)

 

I wish to calculate the number of days between the date_entered column and the date_closed column when the status column = Closed.

 

Thanks

Need Help

5 Replies

  • JorgePinho's avatar
    JorgePinho
    Solution Sage
    3 years ago

    Is that a measure or a new column?

     

    If it is a measure you have to use something like: DATEDIFF(MAX([date_entered]),MAX([date_closed]),DAY)

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    This is a new column.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Looking at this a little deeper, I believe there is a problem with the source data and it not being in Date format.

     

    I've tried cleaning up the date data but that produces a different error.

    • v-yueyunzh-msft's avatar
      v-yueyunzh-msft
      Community Support
      3 years ago

      Hi , Anonymous 

      If you use the DATEDIFF() function, you must check the first two parameters are "Date" or "Date/Time" format.

      If it's not a date format, you can modify your date format in Power Query Editor.

       

      Best Regards,

      Aniya Zhang

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