cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kmarkvenas
Helper I
Helper I

#Duration function

Hi,

I am trying to add a column field with the number of minutes to a Date/Time field in PowerBI Destop. According to my research I can use the #duration function the field to a duration and then add to the Date/Time field, however I cannot find the #duration option in PowerBI.

 

Everytime I try to use #duration in PowerBI desktop, it references the DURATION function which is used for monetary calculations.

Example: 

_First Response Calc = 'RowData'[First Response Target] + #duration('RowData'[First Response Deviation in Minutes])
 
Each time i try to use this #duration gets changed to DURATION and it tells me I am missing some of the parameters?
 
What happened to the #duration function?
 
Thanks,
Kevin

 

 

 

1 ACCEPTED SOLUTION
mattww
Responsive Resident
Responsive Resident

This should do it

 

mattww_0-1633033233817.png

 

Add column > Custom column

 

[First Response Target] + #duration(0,0,[First Response Deviation in Minutes],0)

View solution in original post

21 REPLIES 21
ThisIsFalse
Resolver I
Resolver I

I responed to this before but deleted the post as I saw a prevoious response and thought it should work, but when I tested it it did not. I ended up with issues using the built in function since it returns a table and a single value. In any event you can use DAX and the fact that arithmatic on Date fields works with the assumption that you are using days so :

 

 

FirstResponseCalc = [First Response Target] - [First Response Deviation in Minutes]/1440

 

 

for a calculated column.

ThisIsFalse_1-1633037586587.png

 

 

Yes, this will work as well. This is easier than adding an additional column. If an additional column needs to be added, does that mean everytime the data set is refreshed the same column will had to be re-added?

Mechanically, a calculated column and a column added in power query are very similar. You can use the same foumula and save a bit of memory if you use a measure, but for now I don't think you need to worry about this. As far as having to 're-add' columns I don't think you do if I understand what you are saying. The calculated Columns are calcuated and stored in memory after the Power M data is loaded, and uses a 'lazy evaluation' method (which means it is a place holder until it is shown on a visual). So you don't have to re-add it.
    Of course you can test it out and see if the column stays.

Adding the additional column using the calculation for minutes was slightly more accurate on the report then doing the calculation by days in the calculated column. But the difference is fairly slim. Either one seems to work fine. 

 

Thanks for your help as well.

Kevin

Probably will not matter for his purposes, I just wanted to solve it using Dax, I actually like using Power M, and have even used it to make some fairly complex fucntions. Power M is very powerful if you know how to use it right.

Yes, I agree with you. Power BI is very powerful. It's a matter of becoming proficient at it which will come with time.

mattww
Responsive Resident
Responsive Resident

Hi @kmarkvenas , are you doing this in DAX or in the Power Query editor. The #duration function is in Power Query so it would be accessible when adding a custom column in your dataset

 

mattww_0-1633028139491.png

 

If you want to do it in DAX, use DATEADD - DATEADD function (DAX) - DAX | Microsoft Docs

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Matt

Hi Matt,

 

Thanks for the information. I am doing it in DAX.  If I try to use the DATEADD function in DAX like this:

 

_First Response Calc = DATEADD('RowData'[First Response Target],'RowData'[First Response Deviation in Minutes]/1440,DAY)
 
I will get an error indicating "A date column containing duplicate dates was specified in the call to function 'DATEADD'. This is not supported.
 
Kevin
mattww
Responsive Resident
Responsive Resident

Is that a measure or calculated column? And same question for [First Response Deviation in Minutes]

'RowData'[First Response Target] is a date/time field from the dataset.

RowData'[First Response Deviation in Minutes] is a whole number of minutes field from the dataset.

Using DAX, I am trying to add the deviation minutes to the First Response Target date.

 

mattww
Responsive Resident
Responsive Resident

Try this

 

_First Response Calc =
RowData[First Response Target]
+ TIME ( 0, 0, RowData[First Response Deviation in Minutes] )

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Matt

I get an error indicating "An argument of function 'TIME' has the wrong data type or the result is too large or too small"

mattww
Responsive Resident
Responsive Resident

Do you have some of the data you're applying this to and a screenshot?

Examples:

First Response Target                                   First Response Deviation in Minutes

------------------------                                 ----------------------------------------

4/4/19                                                         -476

5/14/19                                                       -1266

6/5/19                                                         16934

6/13/19                                                       814

7/2/19                                                        -117001

mattww
Responsive Resident
Responsive Resident

Ah ok, the issue is because your durations go beyond 24 hours, see here

 

TIME function (DAX) - DAX | Microsoft Docs

 

DAX isn't ideal for this kind of thing, you would probably find it much easier to go back to using #duration and creating the column in Power Query. Is there a reason you need to do it in DAX?

Ok, I see the Time doesn't support the duration beyond 24 hours. I was using DAX because I was trying to replicate a report that was written in Tableau initially. Tableau uses this function for a calculated field to calculate the difference between the Date/Time Opened field and the First Response Target date field + the Deviation Minutes.

 

(DATEDIFF('minute',[Date/Time Opened],[First Response Target])
+ [First Response Deviation in Minutes]) / 60

 

PowerBI DateDiff was giving me bad results when trying to do a similar thing:

 

_First Response (hours) = DATEDIFF('RowData'[Date/Time Opened],'RowData'[First Response Target] + 'RowData'[First Response Deviation in Minutes], MINUTE) /60

 

Thanks so much for your help. I will trying using the #duration function and creating the column using Power Query.

 

Kevin

mattww
Responsive Resident
Responsive Resident

Ah yeah, the date functions in Power BI can be a bit fiddly compared to Tableau.

 

It would probably be possible in DAX if you really needed to, it would involve calculating the number of days, hours, minutes and seconds. It's probably not worth the hassle

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Matt

Thanks again Matt for your help. Once I figure it out using Power Query I will post the solution here.

mattww
Responsive Resident
Responsive Resident

This should do it

 

mattww_0-1633033233817.png

 

Add column > Custom column

 

[First Response Target] + #duration(0,0,[First Response Deviation in Minutes],0)

View solution in original post

Matt,

Added the Custom Column using Power Query worked great. 

Thanks,

Kevin

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.