Power BI - Custom Calendar Creation - WK1 Extra
Creating a corporate calendar is simple when you know what you're doing. In this video, we'll build the bones of a calendar, no matter where you find a calendar, this is the rough format that they will take, so you can either start with this, or you can use another one. The main thing is to understand what is being done and why.
Copy the text below to get started
**********
let
CurrentDate=Date.From(DateTime.LocalNow()), // Today
// Update the date you want your calendar table to start here the format is yyyy, mm, dd
// ONLY EDIT THESE VALUES
StartDate = #date(1900, 1, 1),
// Update the date you want your calendar table to end here the format is yyyy, mm, dd
// ONLY EDIT THESE VALUES
EndDate = #date(2100, 12, 31),
// Create list of dates between start and finish
Dates = List.Dates(StartDate, Duration.Days( EndDate - StartDate )+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Dates"}}),
#"Inserted Month" = Table.AddColumn(#"Renamed Columns", "Month", each Date.Month([Dates]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.ToText([Dates], "MMM")),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month Name", "Day of Week", each Date.DayOfWeek([Dates],Day.Monday), Int64.Type),
#"Inserted Year" = Table.AddColumn(#"Inserted Day of Week", "Year", each Date.Year([Dates]), Int64.Type),
CreatedYearMonth = Table.AddColumn(#"Inserted Year", "YearMonth", each Text.From ([Year]) & Text.PadStart(Text.From([Month]),2,"0")),
#"Added Month Year" = Table.AddColumn(CreatedYearMonth, "Month Year", each [Month Name]&"-"&Text.From([Year])),
#"Added DayDrift" = Table.AddColumn(#"Added Month Year", "DayDrift", each Duration.Days(Duration.From([Dates] - CurrentDate))),
#"Added MonthDrift" = Table.AddColumn(#"Added DayDrift", "MonthDrift", each ([Year]-Date.Year(CurrentDate))*12+([Month]-Date.Month(CurrentDate))),
#"Added YearDrift" = Table.AddColumn(#"Added MonthDrift", "YearDrift", each [Year]-Date.Year(CurrentDate)),
SetColumndatadypes = Table.TransformColumnTypes(#"Added YearDrift",{{"YearMonth", Int64.Type}, {"Month Year", type text}, {"DayDrift", Int64.Type}, {"MonthDrift", Int64.Type}, {"YearDrift", Int64.Type}})
in
SetColumndatadypes
**********
Join us at https://geordieintelligence.co.uk, read the articles supporting this series; become a member to get the inside track.
Support us: https://geordieintelligence.co.uk/sup...