Latest Enterprise DNA Initiatives

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

Query Editor
#1

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)

#2

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],

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

    //new columns added here
    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
    Add_L12MandCFY

.
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)

I hope this is helpful.

1 Like
#3

Many thanks, @Melissa

You are correct, my Vlookup should read index 3 for Last 12 mths+Bud FY21.
Regards,
@ammu

#4

Hi @ammu, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

#5

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!