Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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 )
HI @dave_adlard
Try swapping the dates
Days Betwn = DATEDIFF ( ( DATEVALUE ( Sheet1[Exposure Start Date] ) ), ( DATEVALUE ( Sheet1[Next Review Date] ) ), DAY )
Thanks for getting back to me unfortunatey I get the same error. Any other ideas
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 )
that worked, thanks so much. Can you please explain to me?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |