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 )
dave_adlard's avatar
dave_adlard
Frequent Visitor
8 years ago

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

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

HI dave_adlard

 

Actually DateDiff takes the following arguments

 

DATEDIFF(<start_date>, <end_date>, <interval>)

 

Startdate can't be later than end_date ....otherwise it will throw an error..

 

Thats why we used Min and Max functions to ensure earlier date comes in the first argument and later date comes in the second argument of DATEDIFF