Forum Discussion
jpt1228
Responsive Resident
Responsive ResidentCustom 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"
ChrisMendoza
Resident Rockstar
Resident Rockstarjpt1228 -
I see you found my response @ https://community.powerbi.com/t5/Desktop/Calendar-table-with-13-periods/m-p/455906#M211260. I'm trying to find a mathematical solution so you do not have to enter [PeriodStart] and [PeriodEnd] for each of period of 28 days; so far no luck.
Possibly like my situation, you'll need to define each 28 day period to get 13 [PeriodIndex].
I reworked the code to attempt to accomadate 13 periods, the day counts are off because I do not know what your first start date was; I started with 01/01/2019 therefore it doesn't look like your sample.
let
Calendar = #table(
{"PeriodStart", "PeriodEnd" },
{
{ #date ( 2019, 01, 01 ), #date ( 2019, 01, 28 ) },
{ #date ( 2019, 01, 29 ), #date ( 2019, 03, 02 ) },
{ #date ( 2019, 03, 03 ), #date ( 2019, 03, 25 ) },
{ #date ( 2019, 03, 26 ), #date ( 2019, 04, 27 ) },
{ #date ( 2019, 04, 28 ), #date ( 2019, 05, 25 ) },
{ #date ( 2019, 05, 26 ), #date ( 2019, 06, 22 ) },
{ #date ( 2019, 06, 23 ), #date ( 2019, 07, 20 ) },
{ #date ( 2019, 07, 21 ), #date ( 2019, 08, 17 ) },
{ #date ( 2019, 08, 18 ), #date ( 2019, 09, 14 ) },
{ #date ( 2019, 09, 15 ), #date ( 2019, 10, 12 ) },
{ #date ( 2019, 10, 13 ), #date ( 2019, 11, 09 ) },
{ #date ( 2019, 11, 10 ), #date ( 2019, 12, 07 ) },
{ #date ( 2019, 12, 08 ), #date ( 2020, 01, 04 ) },
{ #date ( 2020, 01, 05 ), #date ( 2020, 02, 01 ) },
{ #date ( 2020, 02, 02 ), #date ( 2020, 02, 29 ) },
{ #date ( 2020, 03, 01 ), #date ( 2020, 03, 28 ) },
{ #date ( 2020, 03, 29 ), #date ( 2020, 04, 25 ) },
{ #date ( 2020, 04, 26 ), #date ( 2020, 05, 23 ) },
{ #date ( 2020, 05, 24 ), #date ( 2020, 06, 20 ) },
{ #date ( 2020, 06, 21 ), #date ( 2020, 07, 18 ) },
{ #date ( 2020, 07, 19 ), #date ( 2020, 08, 15 ) },
{ #date ( 2020, 08, 16 ), #date ( 2020, 09, 12 ) },
{ #date ( 2020, 09, 13 ), #date ( 2020, 10, 10 ) },
{ #date ( 2020, 10, 11 ), #date ( 2020, 11, 07 ) },
{ #date ( 2020, 11, 08 ), #date ( 2020, 12, 05 ) },
{ #date ( 2020, 12, 06 ), #date ( 2021, 01, 02 ) }
}
),
#"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" ),
#"Removed PeriodStartPeriodEnd" = Table.RemoveColumns ( #"Expanded DatesBetween", { "PeriodStart", "PeriodEnd" } ),
#"Changed Type To Date" = Table.TransformColumnTypes(#"Removed PeriodStartPeriodEnd",{{"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),
#"Inserted Week of Year" = Table.AddColumn(#"Added Fiscal Period", "Week of Year", each if Date.WeekOfYear([Date]) = 53 then 1 else Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Year" = Table.AddColumn(#"Inserted Week of Year", "Year", each Date.Year([Date]), Int64.Type)
in
#"Inserted Year"
jpt1228Responsive Resident
Responsive ResidentChrisMendoza Yes I did, I was trying to figure out how to re-do the M code for 12 periods into 13 but it looks like you did it in your example so thanks for that. Can you explain the logic of dividing the period index? I see there is division of PeriodIndex / 13. What is the rest of the logic? = 0 then 13 else Number.Mod...
#"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),
This custom fiscal period issue in by DimDate has been the most frustrating. Would be nice if someone smarter than me could create an option to generate a non-standard date table. Enter the dates as a parameter for start and stop or # of days in each period and then generate a date table. Most examples are for standard calendar months, modifying the start month but keeping periodicy to months which are no help.
- ChrisMendoza
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'.
- jpt1228
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"- ChrisMendoza
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" }, . . . }), . . .