01-31-2017 03:24 PM
I am trying to do something that is very easy in Excel but haven't found the correct syntax in Power BI yet.
I need to create a measure that calcualtes the number of days between two dates. I am not looking to do anything special like network days just plain old calendar days between dates in Column A to dates in Column B. What is the simpliest way to calulate this in Power BI?
Solved! Go to Solution.
01-31-2017 03:44 PM - edited 03-17-2017 08:05 AM
An error is returned if start_date is larger than end_date.
To avoid this error use this formula in a Calculated Column
Days Column = SWITCH ( TRUE (), 'Table'[start_date] < 'Table'[end_date], DATEDIFF ( 'Table'[start_date], 'Table'[end_date], DAY ), 'Table'[start_date] > 'Table'[end_date], DATEDIFF ( 'Table'[end_date], 'Table'[start_date], DAY ) * -1, 0 )
You multiply * -1 to get negative numbers where the event ended before it started (implying time travel )
Here's the result...
Hope this helps!
01-31-2017 04:18 PM
What is the correct syntax? Assume I am trying to create a Column and have the number of days between the dates in two columns. DateDiff requires an interval like month, year, day, etc. When I try to create a column using the following
Column = DATEDIFF(Table1[Dates1], Table1[Dates2], DAY)
I get an error that the start date cannot be greater than the end date. I switch the order of dates columns and it still returns the same error.
01-31-2017 04:25 PM
Even if you have only 1 row where the end_date is before the start_date you'll get an error for the entire column!
So use the above formula and the negative numbers in the results will show you where that error(s) is(are).
02-06-2017 06:26 PM
Please mark the right reply as answer, so the people who have similar issue can find solution easily and clearly. Thanks a lot.
03-17-2017 06:07 AM
I am not sure how it's working for you but when applied exactly the same formula for a new measure it does not allow me to put the column names like 'start date' and 'end date'. It only accept the formula or measure already created...Any suggestions?
03-17-2017 08:02 AM - edited 03-17-2017 08:04 AM
The above formula is for a Calculated Column
When you try to create a Measure
Intellisense does not allow you to select a column because you need an aggregator first such as MIN or MAX
So here is the Measure formula
Days Measure = SWITCH ( TRUE (), MIN('Projects'[start_date]) < MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[start_date]), MIN('Projects'[end_date]), DAY ), MIN('Projects'[start_date]) > MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[end_date]), MIN('Projects'[start_date]), DAY )* -1 )
And here's the result...
Hope this helps!