Kategorien
Allgemeines PowerBI

Power BI Calendar Script v2

This is a updated version of the power bi global calendar that I use, maybe it will be usefull for you:)

/**
* @author Philipp Frenzel
* @version 0.2.0
* @alias Calendar
* Will create the dimension Calendar
**/

let
  varStartDate = #date(2008,1,1)
  ,Source = List.Dates
  ,#"Invoked FunctionSource" = Source(
    varStartDate
    , Duration.Days(DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),730)) - 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 DateAsInteger" = Table.AddColumn(#"Added QuarterYear", "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 Rolling60Months" = Table.AddColumn( #"Added Rolling25Months", "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
  AddTimeToDateNumber

Schreibe einen Kommentar