# M code to add a new column for 24 months based on current month

Hi Team,
I would like to add “Last 12 Months and Next 12 months” (24 months running)column and “Last 12 Months + Budget FY 2021” column.

I have attached the date table and expected result highlighted in Yellow. "M code with Power query will be a great help as I am doing manually at the moment.
Thank you.
Q12Mths24Mths&12Mths+Bud.xlsx (94.3 KB)

Hi @ammu,

Note that your VLOOKUP for “Last 12 Months + Budget FY 2021” pointed to the wrong index column.
See how this works for you.

``````let
Source = Excel.Workbook(File.Contents(FileLocation), null, true),
tSample_Table = Source{[Item="tSample",Kind="Table"]}[Data],

FYStartMonth = 7,
CurrentDate = Date.From(DateTime.FixedLocalNow()),
IdentifyCurrentDate = Table.SelectRows(tSample_Table, each ([Date] = CurrentDate)),
CurrentYear = IdentifyCurrentDate{0}[CY],
CurrentMonth = IdentifyCurrentDate{0}[CM],

InsertFYoffset = Table.AddColumn(tSample_Table, "FiscalYearOffset", each try (if [CM] >= FYStartMonth then [CY]+1 else [CY]) -
(if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
Add_LorN12M = Table.AddColumn(InsertFYoffset, "Last or Next 12M", each Date.IsInPreviousNMonths( [Date], 12) or Date.IsInCurrentMonth( [Date] ) or Date.IsInNextNMonths( [Date], 11), type logical),
Add_L12MandCFY = Table.AddColumn(Add_LorN12M, "Last 12M or CFY", each Date.IsInPreviousNMonths( [Date], 12) or [FiscalYearOffset] =0, type logical )
in
``````

.
If you add the full file path to the FileLocation Parameter list and select it, the query will be restored.
Date table extensions.pbix (47.9 KB)