I'm trying to calculate the diiference in hours between two date/time columns. When I use DateDiff I am not able to use the two columns below.
Also can use a measure, i feel little faster loading time with this
TimeDiff = CONVERT(SELECTEDVALUE(Table[Date1],0)-SELECTEDVALUE(Table[Date2],0),DATETIME)
and then change the format to time hh:mm:ss
DateDiff_Big...In PowerBI this function doesnt work.
Your Measure should be look like this:
I'm confused. When I type DateDiff( I don't get the TimeFrom or TimeTo columns for selection in the measure.
If I use a calculated column I do but get the above error.
In your scenario, please take a look at @Sean's first reply. You need to create a calculated column instead of measure by clicking "New Column" button. As you mentioned the error occurs when you create a calculated column, would you please share a screenshot about the DAX expression for this column? And which visual do you use to display this calculated column values?
From the image, the custom column DAX expression is correct. And I havn't seen the table visual displays the error you mentioned. Would you mind sharing the .pbix file for us to analyze closely?
@android1 You have to create a COLUMN - NOT a Measure!
Look at the picture in my post above - you can't do this as a Measure - that's why the columns are not showing up when you type!
click New Column - then you'll see the columns will show up after you type DATEDIFF
@android1 So you've verified both columns are date/time.
Then maybe you have some start (FROM) times that are after the corresponding end (TO) times.
See if this works - but don't forget that this means some of your data is not in the right order...
Hours = SWITCH( TRUE(), 'Table'[TimeFrom]<'Table'[TimeTo], DATEDIFF('Table'[TimeFrom], 'Table'[TimeTo], HOUR), 'Table'[TimeFrom]>'Table'[TimeTo], DATEDIFF('Table'[TimeTo], 'Table'[TimeFrom], HOUR), 0 )
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!