Forum Discussion

drgrd13's avatar
drgrd13
Helper I
4 years ago
Solved

datediff column -Power bi

Hi
I want to create the same formula in power bi from Excel. But not too sure how to achieve this. the main formula used in Excel in column to DUE is =IF([@PromiseDt]=0,"Uncommitted",[@PromiseDt]-MAX(TODAY(),[@DueDate]))

DueDate               PromiseDt         to Due
27-Jan-22                   Uncommitted
01-Feb-22 22-Apr-2022-5
17-Apr-22 06-May-20229
08-Aug-2208-Aug-220
12-Oct-2227-Apr-2022-168
17-Apr-228-May-202221
07-Jun-2212-Jul-202235
12-Jul-2212-Jul-220
   



 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi drgrd13 

    you may Create a new Column 

     

     

    To Due =
IF (
    ISBLANK ( Table[PromiseDt] ),
    "Uncommitted",
    DATEDIFF ( Table[PromiseDt], MAX ( TODAY (), Table[DueDate] ), DAY )
)

     

     

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi drgrd13 

    you may Create a new Column 

     

     

    To Due =
IF (
    ISBLANK ( Table[PromiseDt] ),
    "Uncommitted",
    DATEDIFF ( Table[PromiseDt], MAX ( TODAY (), Table[DueDate] ), DAY )
)

     

     

  • drgrd13's avatar
    drgrd13
    Helper I
    4 years ago

    Thank you for the help but the formula is resulting in an error msg as below

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      drgrd13 

      Yes this is not allowed. You can retun blank 

      To Due = IF ( NOT ISBLANK ( Table[PromiseDt] ),  DATEDIFF ( Table[PromiseDt], MAX ( TODAY (), Table[DueDate] ), DAY ) )

       

      Or otherwise, you can return the date as string.