Create a custom index column (for month or week)

B

bkrol

New member
Joined
Apr 28, 2019
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2013
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
 
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?
 
Ron Coderre said:
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?
Click to expand...

The rules are a little fuzzy. I instead used the VLOOKUP - True approach to approach to fill in the fiscal month column.
 
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?
 
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
 
This M-Code creates a table of Fiscal End of Month Dates based on the CurrYear value in the query:
Code: 
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:
Code: 
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?
 

Attachments

  • FiscalCalendar v2.xlsx
    17.5 KB · Views: 7
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.
 

Attachments

  • FMnth_Lkup.xlsx
    16.6 KB · Views: 9
  • Fiscal Calendar Vlookup v02 - with manual month index.xlsx
    133.3 KB · Views: 15
You must log in or register to reply here.
Back
Top