Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

#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

 

 

 

Need Help
  • mattww's avatar
    mattww
    4 years ago

    This should do it

     

     

    Add column > Custom column

     

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

21 Replies

  • mattww's avatar
    mattww
    Responsive Resident
    4 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        mattww
        Responsive Resident
        4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.