Forum Discussion

dave_adlard's avatar
dave_adlard
Frequent Visitor
8 years ago
Solved

In DATEDIFF function the start date cannot be greatert than the end date

This is tipping me over the edge. I have an excel spreadsheet that I have imported into Power BI.   I have filtered the result so only 3 records appear. The next review date are the 9th, 12th and ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI dave_adlard

     

    Give this a shot

     

    Days Betwn =
VAR MinDate =
    MIN (
        DATEVALUE ( Sheet1[Exposure Start Date] ),
        DATEVALUE ( Sheet1[Next Review Date] )
    )
VAR MaxDate =
    MAX (
        DATEVALUE ( Sheet1[Exposure Start Date] ),
        DATEVALUE ( Sheet1[Next Review Date] )
    )
RETURN
    DATEDIFF ( MinDate, MaxDate, DAY )
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

HI dave_adlard

 

Try swapping the dates

 

Days Betwn =
DATEDIFF (
    ( DATEVALUE ( Sheet1[Exposure Start Date] ) ),
    ( DATEVALUE ( Sheet1[Next Review Date] ) ),
    DAY
)
  • dave_adlard's avatar
    dave_adlard
    Frequent Visitor
    8 years ago

    Thanks for getting back to me unfortunatey I get the same error. Any other ideas

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      HI dave_adlard

       

      Give this a shot

       

      Days Betwn =
VAR MinDate =
    MIN (
        DATEVALUE ( Sheet1[Exposure Start Date] ),
        DATEVALUE ( Sheet1[Next Review Date] )
    )
VAR MaxDate =
    MAX (
        DATEVALUE ( Sheet1[Exposure Start Date] ),
        DATEVALUE ( Sheet1[Next Review Date] )
    )
RETURN
    DATEDIFF ( MinDate, MaxDate, DAY )
      • dave_adlard's avatar
        dave_adlard
        Frequent Visitor
        8 years ago

        that worked, thanks so much. Can you please explain to me?