Non Standard Calendar June 18 - June 19

[MrRosco]

Hi,

I am doing a piece of work with one of my customers data, unfortunately they do not use a standard calendar the financial year is between 29th June 18 - 27th June 19.

i have attached a link to the excel spreadsheet with the week commencing date / Month and period number. if anyone knows the DAX formula to convert this it would be greatly appreciated, I just don't know where to start

i have color coordinated the headers to show level of importance

Green - Must have
Amber - Would be nice
Red - Only if Possible

https://www.dropbox.com/s/a78kbcdbzp6okyp/PowerBICalendar.xlsx?dl=0

Thanks in advance
Rosco

 

[VBA Geek]

As far as I can see, your calendar follows a 4-4-5 rule

The below M code for will create the custom calendar which you can load into your data model using Power query. The only thing you need to change is at the very end:

CreateWholeCalendar(#date(2018,6,29),2019,5)

#date(2018,6,29) is the first day of your first period of your custom calendar
2019 is the fiscal year associated to the first day you entered for the previous argument
5 is how many fiscal years you wish to include in your calendar


just copy paste the below into a new blank query and change the last parameters (PS they could also be made dynamic based on what is entered on a sheet)

let
    CreateCustomCalendar = (StartDate as date, FiscalYear as number) as table =>
    let
        InitialDates = Table.FromList(
                                    List.Dates(StartDate,364, #duration(1,0,0,0)),
                                    Splitter.SplitByNothing(),
                                    type table [Date=Date.Type],
                                    null,
                                    ExtraValues.Ignore),


        Tbl28 = Table.AlternateRows(InitialDates,56,35,56),
        Tbl35 = Table.AlternateRows(InitialDates,0,56,35),


        Tbl28AddIndex = Table.AddIndexColumn(Tbl28,"Indx",0,1),
        Tbl28AddInt   = Table.AddColumn(Tbl28AddIndex, "FiscalPeriodNumber", each Number.IntegerDivide([Indx],28) + Number.IntegerDivide([Indx],56) + 1 , Int64.Type),


        Tbl35AddIndex = Table.AddIndexColumn(Tbl35, "Indx",0,1),
        Tbl35AddInt = Table.AddColumn(Tbl35AddIndex,"FiscalPeriodNumber", each (1+Number.IntegerDivide([Indx],35)) * 3, Int64.Type),


        CombineTbl = Table.SelectColumns(Table.Sort(Table.Combine({Tbl28AddInt,Tbl35AddInt}), {"Date",Order.Ascending}),{"Date","FiscalPeriodNumber"}),
    
        // add additional info columns
        CombineTblAddIndex = Table.AddIndexColumn(CombineTbl,"FiscalYearDateSequence",0,1),
        AddFiscalWeekNum = Table.AddColumn(CombineTblAddIndex,"FiscalWeekNumber",each Number.IntegerDivide([FiscalYearDateSequence],7)+1, Int64.Type),
        AddCalendarWeek = Table.AddColumn(AddFiscalWeekNum, "CalendarWeek", each Date.WeekOfYear([Date],Day.Sunday), Int64.Type),
        AddMonthName = Table.AddColumn(AddCalendarWeek, "MonthName", each Date.MonthName([Date]), type text),
        AddWeekDayName = Table.AddColumn(AddMonthName, "WeekdayName", each Date.ToText([Date],"dddd"), type text),
        AddFiscalYear  = Table.AddColumn(AddWeekDayName,"FiscalYear", each FiscalYear, Int64.Type)
    in
        AddFiscalYear,




        CreateWholeCalendar = (InitialDate as date, InitialFiscalYear as number, NumberOfCalendars as number) as table =>
                    Table.Combine(List.Accumulate({1..NumberOfCalendars},
                                                  {},
                                                 (state,current) => state & {CreateCustomCalendar(Date.AddDays(InitialDate, (current -1) * 364), 
                                                                                                  InitialFiscalYear + current - 1)}
                                                 ))
    
    
in
    CreateWholeCalendar(#date(2018,6,29),2019,5)

 

[MrRosco]

This seems to have done the trick!

thank you very much for your help on this.

as always this is a fantastic community

Rosco