Forum Discussion

tobiasmcbride's avatar
tobiasmcbride
Helper III
6 years ago

Power Query today plus 2 years

Hi,

 

I have a Power Query which produces an expanded list of dates from the start and ed date columns in my table. One part is:

 

if EndDate = null then (Date.From ( DateTime.LocalNow() ) else EndDate

 

This means that the end date is effectively taken as today's date if nothing is given there. However, I want this to dynamically change to be today's date plus 2 years such that when I refresh the data it automatically updates this. This is so that I can look ahead to forecasting figures rather than taking a backward looking view.

 

Any ideas greatly appreciated!

Need Help

6 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    tobiasmcbride 

     

    Did you try Date.AddYears()?

     

    See below.

     

    If it helps, mark it as a solution

    Kudos are nice too

    • tobiasmcbride's avatar
      tobiasmcbride
      Helper III
      6 years ago

      ok brill thanks. What if I wanted the date to be the maximum of a particular column (e.g. the maximum date in the end date field) rather than a 2 years fixed from each day going forward?

      • VasTg's avatar
        VasTg
        Memorable Member
        6 years ago

        tobiasmcbride 

         

        You  need to user GROUP BY as below.

        After that expand the Expansion column.

         

        Then create a column as below.

         

        Then delete the unnecessary columns.

         

        If it hleps, mark it as a solution

        Kudos are nice too