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.
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:
Solved! Go to Solution.
This should do it
Add column > Custom column
[First Response Target] + #duration(0,0,[First Response Deviation in Minutes],0)
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.
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.
Hi @Anonymous , 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
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:
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.
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"
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
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
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.
This should do it
Add column > Custom column
[First Response Target] + #duration(0,0,[First Response Deviation in Minutes],0)
Matt,
Added the Custom Column using Power Query worked great.
Thanks,
Kevin
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |