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 12th again of March 2018 and the exposure start daes are  1st April 2018.

 

My new colum is

Days Betwn = DATEDIFF(

(DATEVALUE(Sheet1[Next Review Date])),
(DATEVALUE(Sheet1[Exposure Start Date]))
,DAY)

and I am hit with the error In DATEDIFF function the start date cannot be greatert than the end date

 

Which is obviously not the case. I have edited the query so the value are dates only or date/time and I still get the same error.

Any help would be appreciated.

Do I need to create it as a measure opposed to a column?

Please help?

  • 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 )

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community 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
        Community 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 )