In order to have a global calendar, that can be shared between multiple datasets – I use the following m-script:
/**
* @author Philipp Frenzel
* @version 0.1.0
* @alias Calendar
* Will create the dimension Calendar
**/
let
varStartDate = #date(2008,1,1)
//varStartDate = #date(2020,5,1)
,Source = List.Dates
,#"Invoked FunctionSource" = Source(
varStartDate
, Duration.Days(DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),1420)) - varStartDate)
, #duration(1, 0, 0, 0)
)
,#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error)
,#"Added Index" = Table.AddIndexColumn(#"Table from List", "Index"
, 1
, 1
)
,GenerateDate = Table.RenameColumns(#"Added Index", {{"Column1", "varDate"}})
/**
* Build all fields related to the year
* - Year 2019
* - YearMonth 201901
* - YearWeek 201932
* - YearShort 19
* - YearToDate true
* - YearToMonth true
* - YearToDatePrevious true
**/
,AddYear = Table.AddColumn(GenerateDate, "Year", each
Date.Year([varDate])
)
,AddYearMonth = Table.AddColumn(AddYear, "YearMonth", each
Date.ToText([varDate],"yyyy/MM")
)
,AddYearWeek = Table.AddColumn(AddYearMonth, "YearWeek", each
Date.ToText([varDate],"yyyy") & "." & Number.ToText(Date.WeekOfYear([varDate]),"00")
)
,AddYearColumn = Table.AddColumn(AddYearWeek, "YearShort", each
Text.From([Year])
, type text
)
,TwoDiggetYear = Table.SplitColumn(AddYearColumn, "YearShort",
Splitter.SplitTextByRepeatedLengths(2), {"YearShort.1", "YearShort.2"}
)
,TwoDiggetYearToInteger = Table.TransformColumnTypes(TwoDiggetYear, {
{"YearShort.1", Int64.Type}
, {"YearShort.2", Int64.Type}
})
,ShortYearRemoveColumn1 = Table.RemoveColumns(TwoDiggetYearToInteger, {"YearShort.1"})
,RenameYearShort = Table.RenameColumns(ShortYearRemoveColumn1, {
{"YearShort.2", "YearShort"}
})
,#"Added YearToDate" = Table.AddColumn(RenameYearShort, "YearToDate", each
Date.IsInYearToDate([varDate])
,type logical
)
,#"Added YearToMonth" = Table.AddColumn(#"Added YearToDate", "YearToMonth", each
if Date.IsInYearToDate([varDate]) or Date.IsInCurrentMonth([varDate])
then true
else false
,type logical
)
,#"Added YearToDatePrevious" = Table.AddColumn(#"Added YearToMonth", "YearToDatePrevious", each
Date.IsInYearToDate(Date.AddDays([varDate],365))
,type logical
)
,YearDone = #"Added YearToDatePrevious"
/**
* Build all fields related to the month
* - Month
**/
,AddedMonth = Table.AddColumn( YearDone, "Month", each
Date.Month([varDate])
)
,#"Added MonthName" = Table.AddColumn(AddedMonth, "MonthName", each
Date.ToText([varDate],"MMM")
)
,#"Added MonthLongName" = Table.AddColumn(#"Added MonthName", "MonthLongName", each
Date.ToText([varDate],"MMMM")
)
,#"Added Day" = Table.AddColumn(#"Added MonthLongName", "Day", each
Date.Day([varDate])
)
,#"Added DayName" = Table.AddColumn(#"Added Day", "DayName", each
Date.ToText([varDate],"ddd")
)
/**
* Build fields for quarter, tertial
* Quarter
**/
,#"Added Quarter" = Table.AddColumn(#"Added DayName", "Quarter", each
Date.QuarterOfYear([varDate])
)
,#"Added QuarterYear" = Table.AddColumn(#"Added Quarter", "QuarterYear", each
Text.From([YearShort]) & "Q" & Number.ToText([Quarter],"00")
)
,#"Added is Quarter Current" = Table.AddColumn(#"Added QuarterYear", "QuarterCurrent", each
Date.IsInNextQuarter(Date.AddQuarters([varDate],1))
)
,#"Added is Quarter Previous" = Table.AddColumn(#"Added is Quarter Current","QuarterPrevious", each
Date.IsInNextQuarter(Date.AddQuarters([varDate],2))
)
/** End of Quarter Logic --------------------------------------------*/
,#"Added DateAsInteger" = Table.AddColumn(#"Added is Quarter Previous", "DateAsInteger", each
Number.FromText(
Number.ToText([Year],"0000")
& Number.ToText([Month],"00")
& Number.ToText([Day],"00")
)
),
CleanColumnTypes = Table.TransformColumnTypes(#"Added DateAsInteger", {
{"Day", Int64.Type}
, {"Index", Int64.Type}
, {"Month", Int64.Type}
, {"Quarter", Int64.Type}
, {"MonthName", type text}
, {"QuarterYear", type text}
, {"Year", Int64.Type}
, {"DayName", type text}
,{"DateAsInteger", type text}
})
,#"Clean DateAsInteger" = Table.ReplaceErrorValues(CleanColumnTypes, {
{"DateAsInteger", null}
})
,#"Added MonthCurrent" = Table.AddColumn(#"Clean DateAsInteger", "MonthCurrent", each
Date.IsInCurrentMonth([varDate])
,type logical
),
#"Added MonthCurrent PY" = Table.AddColumn(#"Added MonthCurrent", "MonthCurrent PY", each
Date.IsInCurrentMonth(Date.AddDays([varDate],365))
,type logical
),
#"Added MonthLastFull" = Table.AddColumn(#"Added MonthCurrent PY", "MonthLastFull", each Date.IsInCurrentMonth(Date.AddMonths([varDate],1))),
#"Added MonthPrevious" = Table.AddColumn(#"Added MonthLastFull", "MonthPrevious", each Date.IsInPreviousMonth([varDate])),
#"Added Rolling12Months" = Table.AddColumn( #"Added MonthPrevious", "Rolling12Months", each
Date.IsInPreviousNMonths(Date.AddMonths([varDate],-1),12)
),
#"Added Rolling25Months" = Table.AddColumn( #"Added Rolling12Months", "Rolling25Months", each
Date.IsInPreviousNMonths(Date.AddMonths([varDate],-1),25)
),
#"Added L13N12Months" = Table.AddColumn( #"Added Rolling25Months", "Last13Next12Months", each
Date.IsInPreviousNMonths(Date.AddMonths([varDate],-13),25)
),
#"Added Rolling60Months" = Table.AddColumn( #"Added L13N12Months", "Rolling60Months", each
Date.IsInPreviousNMonths(Date.AddMonths([varDate],-1),60)
),
#"Added Rolling12MonthsPrevious" = Table.AddColumn( #"Added Rolling60Months", "Rolling12MonthsPrevious", each
Date.IsInPreviousNMonths(Date.AddMonths(Date.AddDays([varDate],365),-1),12)
)
,AddedNext1Month = Table.AddColumn(#"Added Rolling12MonthsPrevious", "Next Month", each
Date.IsInPreviousNMonths(Date.AddMonths([varDate],-1),1)
)
,AddedNext3Month = Table.AddColumn(AddedNext1Month, "Next 3 Months", each
Date.IsInPreviousNMonths(Date.AddMonths([varDate],-3),3)
)
,AddedNext12Month = Table.AddColumn(AddedNext3Month, "Next 12 Months", each
Date.IsInPreviousNMonths(Date.AddMonths([varDate],-12),12)
)
,returnMe = AddedNext12Month,
returnMeToo = Table.AddColumn(returnMe, "Rolling12MonthsTimescale", each Number.RoundDown( (if [Rolling12Months] then Duration.Days(Duration.From([varDate] - DateTime.Date(DateTime.FixedLocalNow()))) else if [Rolling12MonthsPrevious] then Duration.Days(Duration.From(Date.AddDays([varDate],365) - DateTime.Date(DateTime.FixedLocalNow()))) else 0) / 31) + 12),
#"Convert Flags to Logical" = Table.TransformColumnTypes(returnMeToo,
{
{"Month", Int64.Type},
{"MonthCurrent", type logical},
{"MonthLastFull", type logical}
}
),
#"Convert varDate to Date" = Table.TransformColumnTypes(#"Convert Flags to Logical", {{"varDate", type date}, {"YearToDate", type logical}, {"YearToDatePrevious", type logical}, {"MonthCurrent", type logical}, {"MonthPrevious", type logical}, {"Rolling12Months", type logical}, {"Rolling12MonthsPrevious", type logical}}),
#"Spalten transformieren" = Table.TransformColumnTypes(#"Convert varDate to Date", {{"YearMonth", type text}, {"MonthLongName", type text}, {"Rolling12MonthsTimescale", Int64.Type}}),
#"Fehler ersetzen" = Table.ReplaceErrorValues(#"Spalten transformieren", {{"YearMonth", null}, {"MonthLongName", null}}),
#"Extract date from eventDateTime" = Table.AddColumn (#"Fehler ersetzen","Date", each DateTime.Date([varDate]),type date),
AddTimeToDate = Table.AddColumn(#"Extract date from eventDateTime","TimeToDate", each
if [varDate] <= DateTime.Date(DateTime.FixedLocalNow())
then true
else false
,type logical)
,AddTimeToDateNumber = Table.AddColumn(AddTimeToDate,"TimeToDateNumber", each
if [varDate] <= DateTime.Date(DateTime.FixedLocalNow())
then 1
else 0
,type number)
in
AddTimeToDateNumberCode-Sprache: PHP (php)