Forum Discussion
Anonymous
7 years agoNot applicable
Custom Date table
Hi guys, Is there a way that I can create a custom date table which has it's start date from 12/31/2018? My team wants the Date table to be in sync with the Fiscal Year Dates and want to see the ...
ChrisMendoza
7 years agoResident Rockstar
Anonymous- My approach to this type of custom need below (unfortunately manually inserting new defined periods is downside):
let
Calendar = #table(
type table
[
#"PeriodStart" = date,
#"PeriodEnd" = date
],
{
{ #date ( 2019, 1, 1 ), #date ( 2019, 2, 3 ) },
{ #date ( 2019, 2, 4 ), #date ( 2019, 3, 2 ) }
}
),
#"Added Index" = Table.AddIndexColumn(Calendar, "PeriodID", 1, 1),
#"Added DatesBetween" = Table.AddColumn(#"Added Index", "Date", each List.Transform( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From (_) ) ),
#"Expanded Date" = Table.SelectColumns(Table.TransformColumnTypes(Table.ExpandListColumn(#"Added DatesBetween", "Date"),{{"Date", type date}}),{"PeriodID", "Date"}),
#"Expanded EndOfWeek" = Table.TransformColumnTypes(Table.SelectColumns(Table.ExpandTableColumn(Table.AddIndexColumn(Table.Group( Table.AddColumn(#"Expanded Date", "End of Week", each Date.EndOfWeek([Date]), type date), {"End of Week"}, {{"EndOfWeek", each _, type table}}), "EndOfWeekID", 1, 1), "EndOfWeek", {"PeriodID", "Date"}, {"PeriodID", "Date"}),{"PeriodID", "EndOfWeekID", "Date" }),{{"PeriodID", Int64.Type},{"EndOfWeekID", Int64.Type},{"Date", type date}}),
#"Added FiscalYearPeriodNum" = Table.AddColumn(#"Expanded EndOfWeek", "FiscalYearPeriodNum", each if Number.Mod([PeriodID], 12) = 0 then 12 else Number.Mod([PeriodID], 12), Int64.Type),
#"Added FiscalYearPeriodName" = Table.AddColumn(#"Added FiscalYearPeriodNum", "FiscalYearPeriodName", each fnSwitchPeriodNumToName([FiscalYearPeriodNum]), type text),
#"Added FiscalYearNum" = Table.AddColumn(#"Added FiscalYearPeriodName", "Fiscal Year", each if [FiscalYearPeriodNum] <= 7 then Date.Year([Date]) else Date.Year([Date]) - 1, Int64.Type),
#"Added FiscalYearPeriodID" = Table.AddColumn(#"Added FiscalYearNum", "FiscalYearPeriodID", each ([Fiscal Year] * 100) + [FiscalYearPeriodNum], Int64.Type),
#"Added FiscalYearPeriodCombined" = Table.RemoveColumns(Table.AddColumn(#"Added FiscalYearPeriodID", "Fiscal Year Period", each Text.Combine({Text.PadStart(Number.ToText([FiscalYearPeriodNum]), 2, "0"), [FiscalYearPeriodName]}, "-"), type text),{"FiscalYearPeriodNum", "FiscalYearPeriodName"})
in
#"Added FiscalYearPeriodCombined"
As long as your period start and end are truly consecutive you should end up with every day in your 'Calendar' that is redefined by subsequent column [Fiscal Year Period]