locked
Help with Duration formula RRS feed

  • Question

  • Please assist calculating duration from a future date column till today.

    Tried the following without success:

    Table.AddColumn(#"YYY", "Variance", each Date.From [Deadline]-(DateTime.LocalNow()), type duration)

    Sunday, August 7, 2016 12:36 PM

Answers

  • Table.AddColumn(#"YYY", "Variance", each Date.From([Deadline])-Date.From(DateTime.LocalNow()), type duration)
    • Proposed as answer by Imke FeldmannMVP Sunday, August 7, 2016 8:36 PM
    • Marked as answer by DZ001 Monday, August 8, 2016 1:52 PM
    Sunday, August 7, 2016 4:26 PM
  • Yes,

    There's a wealth of information on this blog and elsewhere abut dealing wit this use case.  You should strongly consider a date dimension table you can create with PQ.  Here's a sample:

    http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

    Working dates are usually maintained  (explicitely) by the user, a simple Excel table will do.   With these 2 tables it shouls be simple to manage the function

    Many thanks. 

    • Marked as answer by DZ001 Monday, August 8, 2016 1:52 PM
    Monday, August 8, 2016 12:51 PM

All replies

  • Table.AddColumn(#"YYY", "Variance", each Date.From([Deadline])-Date.From(DateTime.LocalNow()), type duration)
    • Proposed as answer by Imke FeldmannMVP Sunday, August 7, 2016 8:36 PM
    • Marked as answer by DZ001 Monday, August 8, 2016 1:52 PM
    Sunday, August 7, 2016 4:26 PM
  • Thank you, Colin,

    Is there a way to calculate it as working days ?

    Monday, August 8, 2016 6:15 AM
  • Yes,

    There's a wealth of information on this blog and elsewhere abut dealing wit this use case.  You should strongly consider a date dimension table you can create with PQ.  Here's a sample:

    http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

    Working dates are usually maintained  (explicitely) by the user, a simple Excel table will do.   With these 2 tables it shouls be simple to manage the function

    Many thanks. 

    • Marked as answer by DZ001 Monday, August 8, 2016 1:52 PM
    Monday, August 8, 2016 12:51 PM