Power Query today plus 2 years

[tobiasmcbride]

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!

[VasTg]

@tobiasmcbride 

Did you try Date.AddYears()?

See below.

If it helps, mark it as a solution

Kudos are nice too

[tobiasmcbride]

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]

@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

[tobiasmcbride]

and what if that end date is in a different table?

[tobiasmcbride]

for instance I see this here (https://stackoverflow.com/questions/50457881/how-to-find-the-most-current-date-from-a-column-in-powe...) but when I try it it only allows me to use columns in the table I then used with this custom function.

[VasTg]

@tobiasmcbride 

You should look at creating this in DAX as a New Column instead of Power Query.