Forum Discussion

NRW_admin1's avatar
NRW_admin1
New Member
10 years ago

Nested IF and DATEDIFF formulas when some date fields are blank

I would like to count the number of days between two date fields: 'datereceived' and 'datedulymade'. I tried to use the DATEDIFF function, BUT - some of the corresponding dates are the same (e.g. num...
  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    10 years ago

    NRW_admin1 You can use DATEDIFF when the dates are the same (see picture below) but you can't have blanks or negative values (to deal with negatives see link at the bottom)

     

    Num of Days =
IF (
    ISBLANK ( 'Table'[Made] ),
    BLANK (),
    DATEDIFF ( 'Table'[Received], 'Table'[Made], DAY )
)

     

     

    Also see response here... (although this doesn't deal with Blank may give some ideas)

    http://community.powerbi.com/t5/Desktop/How-to-use-DATEDIFF-when-End-Date-is-less-than-Start-Date/m-p/37527#M13755

    • NRW_admin1's avatar
      NRW_admin1
      New Member
      10 years ago

      Problem solved, combined with guidance in the link. Many thanks :)

      • Eric_Zhang's avatar
        Eric_Zhang
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        NRW_admin1
        It is great to hear the problem got solved.

         

        It would be greatly appreciated of you if  you can post the solution and accepting it as solution can help the people who would have the same problem to find the answer quickly. :)