none
Calendar Function with Fiscal Year Offset RRS feed

  • Question

  • Hi there, I have a calendar function with offsets for Month, quarter, and calendar year.

    I can't work out however how to add a fiscal year offset? I know i need the current date to extract the current year and worko out which fiscal year it belongs to.

    Here is my current code, 

    //CalendarGenerator with June Year End and Fiscal Periods
    let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
      let
        EndFiscalYearMonth = 6,
        CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
        DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
        Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
        TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
        ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
        RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
        InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
        InsertQuarter = Table.AddColumn(InsertYear, "Quarter Number", each Date.QuarterOfYear([Date])),
        InsertMonth = Table.AddColumn(InsertQuarter, "Month Number", each Date.Month([Date])),
        InsertDay = Table.AddColumn(InsertMonth, "Day Of Month", each Date.Day([Date])),
        InsertDayInt = Table.AddColumn(InsertDay, "Date ID", each [Year] * 10000 + [Month Number] * 100 + [Day Of Month]),
        InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text),
        InsertMonthInt = Table.AddColumn(InsertMonthName, "Month ID", each [Year] * 100 + [Month Number]),
        InsertMonthEnding = Table.AddColumn(InsertMonthInt, "Month Ending", each Date.EndOfMonth([Date]), type date),
        InsertCalendarMonth = Table.AddColumn(InsertMonthEnding, "Month In Calendar", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year])),
        InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter Name", each Number.ToText([Year]) & " Q" & Number.ToText([Quarter Number])),
        InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Weekday Number", each Date.DayOfWeek([Date]) + 1),
        InsertDayName = Table.AddColumn(InsertDayWeek, "Weekday Name", each Date.ToText([Date], "dddd", Culture), type text),
        InsertWeekEnding = Table.AddColumn(InsertDayName, "Week Ending", each Date.EndOfWeek([Date]), type date),
        InsertDateOffset = Table.AddColumn(InsertWeekEnding, "Relative Date Offset", each Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow()) - [Date])), type date),
        InsertWeekOffset = Table.AddColumn(InsertDateOffset, "Relative Week Offset", each Number.RoundUp(Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow()) - [Week Ending]))/7), type date),
        FiscalMonthNo = Table.AddColumn(InsertWeekOffset, "Fiscal Month Number", each if [Month Number] > EndFiscalYearMonth  then [Month Number]-EndFiscalYearMonth  else [Month Number]+EndFiscalYearMonth,type text),
        FiscalYear = Table.AddColumn(FiscalMonthNo, "Fiscal Year", each if [Fiscal Month Number] <=EndFiscalYearMonth  then [Year]+1 else [Year]),
        FiscalQtr = Table.AddColumn(FiscalYear, "Fiscal Quarter", each if [Fiscal Month Number] >= 10 then "FQ-4" else if [Fiscal Month Number] >= 7 then "FQ-3" else if [Fiscal Month Number] >= 4 then "FQ-2" else "FQ-1"),
        DataTypes = Table.TransformColumnTypes(FiscalQtr,{{"Fiscal Quarter", type text}, {"Weekday Name", type text}, {"Quarter Name", type text}, {"Month In Calendar", type text}, {"Month Name", type text}, {"Year", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Number", Int64.Type}, {"Day Of Month", Int64.Type}, {"Date ID", Int64.Type}, {"Month ID", Int64.Type}, {"Weekday Number", Int64.Type}, {"Relative Date Offset", Int64.Type}, {"Relative Week Offset", Int64.Type}, {"Fiscal Month Number", type text}, {"Fiscal Year", Int64.Type}}),
        CurrentMonthOffset = Table.AddColumn(DataTypes, "CurrentMonthOffset", each (Date.Year([Date])- Date.Year(CurrentDate) ) * 12 + Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
        CurrentQuarterOffset = Table.AddColumn(CurrentMonthOffset, "CurrentQuarterOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) )*4 + Number.RoundUp(Date.Month([Date]) / 3) - Number.RoundUp(Date.Month(CurrentDate) / 3), Int64.Type),
        CurrentYearOffset = Table.AddColumn(CurrentQuarterOffset, "CurrentYearOffset", each Date.Year([Date]) - Date.Year(CurrentDate), Int64.Type)
      in
        CurrentYearOffset,
          Custom1 = CreateDateTable
    in
      Custom1

    Has anyone got any ideas how i can achieve this offset for a DYNAMIC fiscal Year OFFSET.

    Thanks in advance for any help!

    Monday, December 9, 2019 3:12 PM

Answers

  • Dax is no good as it's not dynamic enough and has to be re-calculated each time.

    Say I want to work out the cost of sales DYNAMICALLY this fiscal year or income this fiscal year. 

    I found the answer and here it is for others interested.

    I think Dax would be slower than the M code but not 100% sure.

    FiscalYearOffset = Table.AddColumn( CurrentYearOffset, "FiscalYearOffset", each [Fiscal Year] - List.Max( Table.SelectRows( CurrentQuarterOffset, (a) => a[Date] = CurrentDate )[Fiscal Year] ) , Int64.Type )

    • Marked as answer by Garry72 Monday, December 9, 2019 11:16 PM
    Monday, December 9, 2019 11:16 PM

All replies

  • I am not providing you the answer, but I suggest another way of coding a calendar :

    let
    StartYear = 2018,
    EndYear = 2019,
    myDateStart= #date(StartYear,1,1),
    myDateEnd = #date(EndYear, 12, 31),
    NumberOfDays = Duration.Days(myDateEnd-myDateStart) +1,
    DateList = List.Dates(myDateStart, NumberOfDays, #duration(1,0,0,0)),
    AddColumns =
    		List.Transform(
    			DateList,
    			each
    			{_, Date.Year(_), "Q" & Text.From(Date.QuarterOfYear(_)),
    			Date.ToText(_, "MMM"), Date.Month(_), Date.Day(_), Date.ToText(_, "yyyy-MM")
    			}
    		),
    DateTable =
    		#table(
    			type table[
    				Date=date, Year=Int64.Type, Quarter=text, 
    				Month=text, Month num=Byte.Type, Day=Byte.Type, #"Year-Month"=text
    				],
    			AddColumns
    		)
    in DateTable

    of course you can add all the fields that you may need.


    • Edited by anthony34 Monday, December 9, 2019 3:49 PM
    Monday, December 9, 2019 3:48 PM
  • Hi and thanks for your comment. 

    I do like your code setup :).

    Would you know how can I add the "Dynamic" fiscal year offset with your method?

    Monday, December 9, 2019 4:26 PM
  • Hi,

    Do you consider DAX? Time-intelligence in DAX is more convenient. For example, for fiscal year you may just specify year's end:

    Sales_YTD_Fiscal := CALCULATE ( [Sales Amount], DATESYTD ( 'Calendar'[Date], "06/30" ) )

    Monday, December 9, 2019 4:46 PM
  • Dax is no good as it's not dynamic enough and has to be re-calculated each time.

    Say I want to work out the cost of sales DYNAMICALLY this fiscal year or income this fiscal year. 

    I found the answer and here it is for others interested.

    I think Dax would be slower than the M code but not 100% sure.

    FiscalYearOffset = Table.AddColumn( CurrentYearOffset, "FiscalYearOffset", each [Fiscal Year] - List.Max( Table.SelectRows( CurrentQuarterOffset, (a) => a[Date] = CurrentDate )[Fiscal Year] ) , Int64.Type )

    • Marked as answer by Garry72 Monday, December 9, 2019 11:16 PM
    Monday, December 9, 2019 11:16 PM