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.
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!
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.
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).
Please mark the right reply as answer, so the people who have similar issue can find solution easily and clearly. Thanks a lot.
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?
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!
I am trying to use your basic formula, my version is :
Days Last Update = SWITCH ( TRUE (), MIN('Production Approval Workflow'[Status_InFlight])=TRUE, DATEDIFF (MIN('Production Approval Workflow'[Modified]), today(), DAY ), MIN('Production Approval Workflow'[Status_InFlight])=FALSE, "" )
But when I drop it into a table, I get the "Can't display the visual" error.
Note: The field [Modifed] iis a date/time stamp.
Also - why don't the table names and field values prepopulate when I am creating a formula?