Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Non Standard Calendar June 18 - June 19

  1. #1
    New Member
    Join Date
    May 2016
    Location
    Surrey, England
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Non Standard Calendar June 18 - June 19


    Non Standard Calendar June 18 - June 19
    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/a78kbcdbzp...ndar.xlsx?dl=0

    Thanks in advance
    Rosco

  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,776
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Post Re: Non Standard Calendar June 18 - June 19

    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)


    Code:
    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)

  3. #3
    New Member
    Join Date
    May 2016
    Location
    Surrey, England
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Non Standard Calendar June 18 - June 19

    This seems to have done the trick!

    thank you very much for your help on this.

    as always this is a fantastic community

    Rosco

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •