Create a custom index column (for month or week)

[bkrol]

I am creating a custom calendar table that covers several years. I want to create an index for things like fiscal month or week.

If I was using excel and the fiscal month was in column A and the fiscal month index was in column B, then I would write a formula like =if(A2<>A1,B1+1,B1) and copy it down the sorted column.

Basically I want to add 1 if there is a change in value for a specific column.

I'm not sure how this is done in Power Query.

Thanks,

Bill

 

[Ron Coderre]

You mention "Fiscal Month"...Implying that they are different from calendar months (i.e. 4-4-5 convention). What are the rules that define a fiscal month and fiscal year?

 

[bkrol]

You mention "Fiscal Month"...Implying that they are different from calendar months (i.e. 4-4-5 convention). What are the rules that define a fiscal month and fiscal year?

The rules are a little fuzzy. I instead used the VLOOKUP - True approach to approach to fill in the fiscal month column.

 

[Ron Coderre]

I wasn't clear in my question... Some fiscal months are 4weeks, 4weeks, 5weeks...repeated for 4 quarters.
Ignore Excel formula rules....what are the rules that determine calendar month start and ends?
Are your fiscal months simply calendar months? Jan, Feb, etc? Or something else?

 

[bkrol]

Ron,
If January 1 falls between Sunday and Wednesday, then the fiscal January ends on the 4th Saturday. If January 1 falls between Thursday and Saturday, then the fiscal January ends on the 5th Saturday.
Then it switches to 4 weeks for February (all weeks run from Sunday to Saturday), 5 weeks for March, and then back to 4-4-5. If there are extra days at the end of the year, they are included in December (so December can be more than 35 days)
Thanks,
Bill

 

[Bill Szysz]

Bill, please give us some example file (manually completed).
By the way...check these links:
https://www.youtube.com/watch?v=I-cDHK_4h1U
https://www.excelguru.ca/blog/2019/04/15/creating-a-fiscal-saturday-calendar/

 

[Ron Coderre]

This M-Code creates a table of Fiscal End of Month Dates based on the CurrYear value in the query:

let
    // These values are the cumulative sum of the fiscal weeks 4,4,5,4,4,5,4,4,5,4,4,5
    BaseWeekCount = {4,8,13,17,21,26,30,34,39,43,47,52},
    ConvertListToTable = Table.FromList(BaseWeekCount, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    WeekCountTable = Table.RenameColumns(ConvertListToTable,{{"Column1", "CumWeeks"}}),

    CurrYear = 2019,
    StartOfYear = #date(CurrYear, 1,1),
    StartOfYearDay = Date.DayOfWeek(StartOfYear),
    FiscalStartOfYearDate = #date(CurrYear,1,8-StartOfYearDay),
    JanWeekAdj = if StartOfYearDay <= 3 then 0 else 1,
    CreateFiscalEndOfMthTable = 
        Table.TransformColumnTypes(
            Table.TransformColumns(
                WeekCountTable, 
                {{"CumWeeks", each (_ + JanWeekAdj)*7+Number.From(FiscalStartOfYearDate)-1 , type number}}),{{"CumWeeks", type date}}),
    RenameDateCol = Table.RenameColumns(CreateFiscalEndOfMthTable,{{"CumWeeks", "EOMDate"}}),
    AddDayNameCol = Table.AddColumn(RenameDateCol, "DayName", each Date.DayOfWeekName([EOMDate]))
in
    AddDayNameCol

For 2019, these are the returned values:

EOMDate             Custom
02/02/2019          Saturday
03/02/2019          Saturday
04/06/2019          Saturday
05/04/2019          Saturday
06/01/2019          Saturday
07/06/2019          Saturday
08/03/2019          Saturday
08/31/2019          Saturday
10/05/2019          Saturday
11/02/2019          Saturday
11/30/2019          Saturday
01/04/2020          Saturday

Is that something you can work with?

 

[bkrol]

Attached is the Calendar Table that I have created so far. It needs the FMnth_Lkup to deal with the fiscal months. I'm just learning so I'm sure it this was an incredibly crude way of doing it.

If you look at the tab with the output calendar you can see that I have written a formula outside the table that creates an index for the F.Mnth number. I'm not sure how to do the equivalent in Power Query.