Forum Discussion
Solved
Custom Fiscal Period True/False ignoring current time
Hello - I have 13 - 4 caIendar week periods in a year (Excluding leap year). Most years there are a couple days from the previous calendar year included in the next fiscal year. I have a custom f...
Hi ChrisMendoza Thanks to your M code to create a non-standard date table I was having an issue where the fiscal dates that fell in the previous calendar year would not show up in the correct fiscal year. If I modified your code to include one more column called FiscalYear and then added , "YEAR" }, to the end of each custom date it would bring in the correct Fiscal year. I have been looking all over for how to do this and couldn't find any M or DAX. Hopefully this will help the next person that has to deal with custom non, calendar fiscal time periods. This is also a great option to account for leap years.
let Calendar = #table( {"PeriodStart", "PeriodEnd", "FiscalYear" }, { { #date ( 2017, 12, 31 ), #date ( 2018, 1, 27 ), "2018" }, { #date ( 2018, 1, 28 ), #date ( 2018, 2, 24 ), "2018" }, { #date ( 2018, 2, 25 ), #date ( 2018, 3, 24 ), "2018" }, { #date ( 2018, 3, 25 ), #date ( 2018, 4, 21 ), "2018" }, { #date ( 2018, 4, 22 ), #date ( 2018, 5, 19 ), "2018" }, { #date ( 2018, 5, 20 ), #date ( 2018, 6, 16 ), "2018" }, { #date ( 2018, 6, 17 ), #date ( 2018, 7, 14 ), "2018" }, { #date ( 2018, 7, 15 ), #date ( 2018, 8, 11 ), "2018" }, { #date ( 2018, 8, 12 ), #date ( 2018, 9, 8 ), "2018" }, { #date ( 2018, 9, 9 ), #date ( 2018, 10, 6 ), "2018" }, { #date ( 2018, 10, 7 ), #date ( 2018, 11, 3 ), "2018" }, { #date ( 2018, 11, 4 ), #date ( 2018, 12, 1 ), "2018" }, { #date ( 2018, 12, 2 ), #date ( 2018, 12, 29 ), "2018" }, { #date ( 2018, 12, 30 ), #date ( 2019, 1, 26 ), "2019" }, { #date ( 2019, 1, 27 ), #date ( 2019, 2, 23 ), "2019" }, { #date ( 2019, 2, 24 ), #date ( 2019, 3, 23 ), "2019"}, { #date ( 2019, 3, 24 ), #date ( 2019, 4, 20 ), "2019" }, { #date ( 2019, 4, 21 ), #date ( 2019, 5, 18 ), "2019" }, { #date ( 2019, 5, 19 ), #date ( 2019, 6, 15 ), "2019" }, { #date ( 2019, 6, 16 ), #date ( 2019, 7, 13), "2019" }, { #date ( 2019, 7, 14 ), #date ( 2019, 8, 10 ), "2019" }, { #date ( 2019, 8, 11 ), #date ( 2019, 9, 7 ), "2019" }, { #date ( 2019, 9, 8 ), #date ( 2019, 10, 5 ), "2019" }, { #date ( 2019, 10, 6 ), #date ( 2019, 11, 2 ), "2019" }, { #date ( 2019, 11, 3 ), #date ( 2019, 11, 30 ), "2019" }, { #date ( 2019, 12, 1 ), #date ( 2019, 12, 28 ), "2019" }, { #date ( 2019, 12, 29 ), #date ( 2020, 1, 25 ), "2020" }, { #date ( 2020, 1, 26 ), #date ( 2020, 2, 22 ), "2020"}, { #date ( 2020, 2, 23 ), #date ( 2020, 3, 21 ), "2020" }, { #date ( 2020, 3, 22 ), #date ( 2020, 4, 18 ), "2020" }, { #date ( 2020, 4, 19 ), #date ( 2020, 5, 16 ), "2020" }, { #date ( 2020, 5, 17 ), #date ( 2020, 6, 13 ), "2020" }, { #date ( 2020, 6, 14 ), #date ( 2020, 7, 11 ), "2020" }, { #date ( 2020, 7, 12 ), #date ( 2020, 8, 8 ), "2020" }, { #date ( 2020, 8, 9 ), #date ( 2020, 9, 5 ), "2020" }, { #date ( 2020, 9, 6 ), #date ( 2020, 10, 3 ), "2020" }, { #date ( 2020, 10, 4 ), #date ( 2020, 10, 31 ), "2020" }, { #date ( 2020, 11, 1 ), #date ( 2020, 11, 28 ), "2020" }, { #date ( 2020, 11, 29 ), #date ( 2021, 1, 2 ), "2020" } } ), #"Added PeriodIndex" = Table.AddIndexColumn(Calendar, "PeriodIndex", 1, 1), #"Added DatesBetween" = Table.AddColumn ( #"Added PeriodIndex", "Date", each List.Transform ( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From ( _ ) ) ), #"Expanded DatesBetween" = Table.ExpandListColumn ( #"Added DatesBetween", "Date" ), #"Changed Type To Date" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"Date", type date}}), #"Added Fiscal Period" = Table.AddColumn(#"Changed Type To Date", "Fiscal Period", each if Number.Mod ( [PeriodIndex], 13 ) = 0 then 13 else Number.Mod([PeriodIndex], 13 ), Int64.Type), InsertYear = Table.AddColumn(#"Added Fiscal Period", "Year", each Date.Year([Date]), type number), InsertQuarter = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date]), type number), InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]),type text), InsertCalendarQtrOrder = Table.AddColumn(InsertCalendarQtr, "Quarter Year Order", each [Year] * 10 + [Quarter Num], type number), InsertMonth = Table.AddColumn(InsertCalendarQtrOrder, "Month Num", each Date.Month([Date]), type number), InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM"), type text), InsertMonthNameShort = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM"), type text), InsertCalendarMonth = Table.AddColumn(InsertMonthNameShort, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text), InsertCalendarMonthOrder = Table.AddColumn(InsertCalendarMonth, "Month Year Order", each [Year] * 100 + [Month Num], type number), InsertWeek = Table.AddColumn(InsertCalendarMonthOrder, "Week Num", each Date.WeekOfYear([Date]), type number), InsertCalendarWk = Table.AddColumn(InsertWeek, "Week Year", each "W" & Number.ToText([Week Num]) & " " & Number.ToText([Year]), type text), InsertCalendarWkOrder = Table.AddColumn(InsertCalendarWk, "Week Year Order", each [Year] * 100 + [Week Num], type number), InsertWeekEnding = Table.AddColumn(InsertCalendarWkOrder, "Week Ending", each Date.EndOfWeek([Date]), type date), InsertDay = Table.AddColumn(InsertWeekEnding, "Month Day Num", each Date.Day([Date]), type number), InsertDayInt = Table.AddColumn(InsertDay, "Date Int", each [Year] * 10000 + [Month Num] * 100 + [Month Day Num], type number), InsertDayWeek = Table.AddColumn(InsertDayInt, "Day Num Week", each Date.DayOfWeek([Date]) + 1, type number), InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd"), type text), InsertWeekend = Table.AddColumn(InsertDayName, "Weekend", each if [Day Num Week] = 1 then "Y" else if [Day Num Week] = 7 then "Y" else "N", type text), InsertDayNameShort = Table.AddColumn(InsertWeekend, "Day Name Short", each Date.ToText([Date], "ddd"), type text), InsertIndex = Table.AddIndexColumn(InsertDayNameShort, "Index", 1, 1), InsertDayOfYear = Table.AddColumn(InsertIndex, "Day of Year", each Date.DayOfYear([Date]), type number), InsertCurrentDay = Table.AddColumn(InsertDayOfYear, "Current Day?", each Date.IsInCurrentDay([Date]), type logical), InsertCurrentWeek = Table.AddColumn(InsertCurrentDay, "Current Week?", each Date.IsInCurrentWeek([Date]), type logical), InsertCurrentMonth = Table.AddColumn(InsertCurrentWeek, "Current Month?", each Date.IsInCurrentMonth([Date]), type logical), InsertCurrentQuarter = Table.AddColumn(InsertCurrentMonth, "Current Quarter?", each Date.IsInCurrentQuarter([Date]), type logical), InsertCurrentYear = Table.AddColumn(InsertCurrentQuarter, "Current Year?", each Date.IsInCurrentYear([Date]), type logical), InsertCompletedDay = Table.AddColumn(InsertCurrentYear, "Completed Days", each if DateTime.Date(DateTime.LocalNow()) > [Date] then "Y" else "N", type text), InsertCompletedWeek = Table.AddColumn(InsertCompletedDay, "Completed Weeks", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.WeekOfYear(DateTime.Date(DateTime.LocalNow())) > Date.WeekOfYear([Date])) then "Y" else "N", type text), InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "Completed Months", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.Month(DateTime.Date(DateTime.LocalNow())) > Date.Month([Date])) then "Y" else "N", type text), InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "Completed Quarters", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow())) > Date.QuarterOfYear([Date])) then "Y" else "N", type text), InsertCompletedYear = Table.AddColumn(InsertCompletedQuarter, "Completed Years", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else "N", type text), #"Renamed Columns" = Table.RenameColumns(InsertCompletedYear,{{"PeriodStart", "FiscPeriodStart"}, {"PeriodEnd", "FiscPeriodEnd"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FiscPeriodEnd", type date}, {"FiscPeriodStart", type date}}), #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "FiscalYearPeriod", each Text.Combine({[FiscalYear], Text.From([PeriodIndex], "en-US")}, "-"), type text) in #"Inserted Merged Column"
jpt1228 -
Number.Mod finds the remainder so when [PeriodIndex] = a multiple of 13 the remainder is 0. If the remainder is 0 then that is what we'll call the 13th period. Honestly, a co-worker told me to use it when I was struggling with my custom calendar.
If you only used
Number.Mod ( [PeriodIndex], 13 )instead of the the if then else you can see where it places the 0's.
Maybe someone else can explain how that works mathematically.
I feel your pain with creating a custom calendar. The calendar I use has custom periods like 01/31/2020 - 02/29/2020 for the 9th Fiscal Period = '09-February'.
Hi ChrisMendoza Thanks to your M code to create a non-standard date table I was having an issue where the fiscal dates that fell in the previous calendar year would not show up in the correct fiscal year. If I modified your code to include one more column called FiscalYear and then added , "YEAR" }, to the end of each custom date it would bring in the correct Fiscal year. I have been looking all over for how to do this and couldn't find any M or DAX. Hopefully this will help the next person that has to deal with custom non, calendar fiscal time periods. This is also a great option to account for leap years.
let
Calendar = #table(
{"PeriodStart", "PeriodEnd", "FiscalYear" },
{ { #date ( 2017, 12, 31 ), #date ( 2018, 1, 27 ), "2018" },
{ #date ( 2018, 1, 28 ), #date ( 2018, 2, 24 ), "2018" },
{ #date ( 2018, 2, 25 ), #date ( 2018, 3, 24 ), "2018" },
{ #date ( 2018, 3, 25 ), #date ( 2018, 4, 21 ), "2018" },
{ #date ( 2018, 4, 22 ), #date ( 2018, 5, 19 ), "2018" },
{ #date ( 2018, 5, 20 ), #date ( 2018, 6, 16 ), "2018" },
{ #date ( 2018, 6, 17 ), #date ( 2018, 7, 14 ), "2018" },
{ #date ( 2018, 7, 15 ), #date ( 2018, 8, 11 ), "2018" },
{ #date ( 2018, 8, 12 ), #date ( 2018, 9, 8 ), "2018" },
{ #date ( 2018, 9, 9 ), #date ( 2018, 10, 6 ), "2018" },
{ #date ( 2018, 10, 7 ), #date ( 2018, 11, 3 ), "2018" },
{ #date ( 2018, 11, 4 ), #date ( 2018, 12, 1 ), "2018" },
{ #date ( 2018, 12, 2 ), #date ( 2018, 12, 29 ), "2018" },
{ #date ( 2018, 12, 30 ), #date ( 2019, 1, 26 ), "2019" },
{ #date ( 2019, 1, 27 ), #date ( 2019, 2, 23 ), "2019" },
{ #date ( 2019, 2, 24 ), #date ( 2019, 3, 23 ), "2019"},
{ #date ( 2019, 3, 24 ), #date ( 2019, 4, 20 ), "2019" },
{ #date ( 2019, 4, 21 ), #date ( 2019, 5, 18 ), "2019" },
{ #date ( 2019, 5, 19 ), #date ( 2019, 6, 15 ), "2019" },
{ #date ( 2019, 6, 16 ), #date ( 2019, 7, 13), "2019" },
{ #date ( 2019, 7, 14 ), #date ( 2019, 8, 10 ), "2019" },
{ #date ( 2019, 8, 11 ), #date ( 2019, 9, 7 ), "2019" },
{ #date ( 2019, 9, 8 ), #date ( 2019, 10, 5 ), "2019" },
{ #date ( 2019, 10, 6 ), #date ( 2019, 11, 2 ), "2019" },
{ #date ( 2019, 11, 3 ), #date ( 2019, 11, 30 ), "2019" },
{ #date ( 2019, 12, 1 ), #date ( 2019, 12, 28 ), "2019" },
{ #date ( 2019, 12, 29 ), #date ( 2020, 1, 25 ), "2020" },
{ #date ( 2020, 1, 26 ), #date ( 2020, 2, 22 ), "2020"},
{ #date ( 2020, 2, 23 ), #date ( 2020, 3, 21 ), "2020" },
{ #date ( 2020, 3, 22 ), #date ( 2020, 4, 18 ), "2020" },
{ #date ( 2020, 4, 19 ), #date ( 2020, 5, 16 ), "2020" },
{ #date ( 2020, 5, 17 ), #date ( 2020, 6, 13 ), "2020" },
{ #date ( 2020, 6, 14 ), #date ( 2020, 7, 11 ), "2020" },
{ #date ( 2020, 7, 12 ), #date ( 2020, 8, 8 ), "2020" },
{ #date ( 2020, 8, 9 ), #date ( 2020, 9, 5 ), "2020" },
{ #date ( 2020, 9, 6 ), #date ( 2020, 10, 3 ), "2020" },
{ #date ( 2020, 10, 4 ), #date ( 2020, 10, 31 ), "2020" },
{ #date ( 2020, 11, 1 ), #date ( 2020, 11, 28 ), "2020" },
{ #date ( 2020, 11, 29 ), #date ( 2021, 1, 2 ), "2020" }
}
),
#"Added PeriodIndex" = Table.AddIndexColumn(Calendar, "PeriodIndex", 1, 1),
#"Added DatesBetween" = Table.AddColumn ( #"Added PeriodIndex", "Date", each List.Transform ( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From ( _ ) ) ),
#"Expanded DatesBetween" = Table.ExpandListColumn ( #"Added DatesBetween", "Date" ),
#"Changed Type To Date" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"Date", type date}}),
#"Added Fiscal Period" = Table.AddColumn(#"Changed Type To Date", "Fiscal Period", each if Number.Mod ( [PeriodIndex], 13 ) = 0 then 13 else Number.Mod([PeriodIndex], 13 ), Int64.Type),
InsertYear = Table.AddColumn(#"Added Fiscal Period", "Year", each Date.Year([Date]), type number),
InsertQuarter = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date]), type number),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]),type text),
InsertCalendarQtrOrder = Table.AddColumn(InsertCalendarQtr, "Quarter Year Order", each [Year] * 10 + [Quarter Num], type number),
InsertMonth = Table.AddColumn(InsertCalendarQtrOrder, "Month Num", each Date.Month([Date]), type number),
InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM"), type text),
InsertMonthNameShort = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthNameShort, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
InsertCalendarMonthOrder = Table.AddColumn(InsertCalendarMonth, "Month Year Order", each [Year] * 100 + [Month Num], type number),
InsertWeek = Table.AddColumn(InsertCalendarMonthOrder, "Week Num", each Date.WeekOfYear([Date]), type number),
InsertCalendarWk = Table.AddColumn(InsertWeek, "Week Year", each "W" & Number.ToText([Week Num]) & " " & Number.ToText([Year]), type text),
InsertCalendarWkOrder = Table.AddColumn(InsertCalendarWk, "Week Year Order", each [Year] * 100 + [Week Num], type number),
InsertWeekEnding = Table.AddColumn(InsertCalendarWkOrder, "Week Ending", each Date.EndOfWeek([Date]), type date),
InsertDay = Table.AddColumn(InsertWeekEnding, "Month Day Num", each Date.Day([Date]), type number),
InsertDayInt = Table.AddColumn(InsertDay, "Date Int", each [Year] * 10000 + [Month Num] * 100 + [Month Day Num], type number),
InsertDayWeek = Table.AddColumn(InsertDayInt, "Day Num Week", each Date.DayOfWeek([Date]) + 1, type number),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd"), type text),
InsertWeekend = Table.AddColumn(InsertDayName, "Weekend", each if [Day Num Week] = 1 then "Y" else if [Day Num Week] = 7 then "Y" else "N", type text),
InsertDayNameShort = Table.AddColumn(InsertWeekend, "Day Name Short", each Date.ToText([Date], "ddd"), type text),
InsertIndex = Table.AddIndexColumn(InsertDayNameShort, "Index", 1, 1),
InsertDayOfYear = Table.AddColumn(InsertIndex, "Day of Year", each Date.DayOfYear([Date]), type number),
InsertCurrentDay = Table.AddColumn(InsertDayOfYear, "Current Day?", each Date.IsInCurrentDay([Date]), type logical),
InsertCurrentWeek = Table.AddColumn(InsertCurrentDay, "Current Week?", each Date.IsInCurrentWeek([Date]), type logical),
InsertCurrentMonth = Table.AddColumn(InsertCurrentWeek, "Current Month?", each Date.IsInCurrentMonth([Date]), type logical),
InsertCurrentQuarter = Table.AddColumn(InsertCurrentMonth, "Current Quarter?", each Date.IsInCurrentQuarter([Date]), type logical),
InsertCurrentYear = Table.AddColumn(InsertCurrentQuarter, "Current Year?", each Date.IsInCurrentYear([Date]), type logical),
InsertCompletedDay = Table.AddColumn(InsertCurrentYear, "Completed Days", each if DateTime.Date(DateTime.LocalNow()) > [Date] then "Y" else "N", type text),
InsertCompletedWeek = Table.AddColumn(InsertCompletedDay, "Completed Weeks", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.WeekOfYear(DateTime.Date(DateTime.LocalNow())) > Date.WeekOfYear([Date])) then "Y" else "N", type text),
InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "Completed Months", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.Month(DateTime.Date(DateTime.LocalNow())) > Date.Month([Date])) then "Y" else "N", type text),
InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "Completed Quarters", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow())) > Date.QuarterOfYear([Date])) then "Y" else "N", type text),
InsertCompletedYear = Table.AddColumn(InsertCompletedQuarter, "Completed Years", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else "N", type text),
#"Renamed Columns" = Table.RenameColumns(InsertCompletedYear,{{"PeriodStart", "FiscPeriodStart"}, {"PeriodEnd", "FiscPeriodEnd"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FiscPeriodEnd", type date}, {"FiscPeriodStart", type date}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "FiscalYearPeriod", each Text.Combine({[FiscalYear], Text.From([PeriodIndex], "en-US")}, "-"), type text)
in
#"Inserted Merged Column"jpt1228 -
Yeah, I don't know how you'd mathematically find the [FYStart] with your need as those dates are dynamic. Seems like your solution/decision to build into the Source Table is best. Glad it helped.
Calendar = #table( {"PeriodStart", "PeriodEnd", "FiscalYear" }, . . . { #date ( 2018, 12, 30 ), #date ( 2019, 1, 26 ), "2019" }, . . . { #date ( 2019, 12, 29 ), #date ( 2020, 1, 25 ), "2020" }, . . . }), . . .