Add a Date Model to PowerBI or Power Query

Add this query to your Power BI or Power Query and you can use it to relate any other existing model to Date. This is more powerful than using the inbuilt date model that comes with PowerBI.

let
  //Capture the date range from the parameters
  StartDate = #date(2015, 01, 01),
  EndDate = #date(2099, 12, 30),
  CurrentFinancialYear = if Date.Month(DateTime.LocalNow()) > 6 then Date.Year(DateTime.LocalNow())+1 else Date.Year(DateTime.LocalNow()),
  //Get the number of dates that will be required for the table
  GetDateCount = Duration.Days(EndDate - StartDate),
  //Take the count of dates and turn it into a list of dates
  GetDateList = List.Dates(StartDate, GetDateCount,
    #duration(1,0,0,0)),
  //Convert the list into a table
  DateListToTable = Table.FromList(GetDateList,
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
  //Convert the Date column to a Date type
  ChangeDateToDateType = Table.TransformColumnTypes(DateListToTable,{{"Date", type date}}),
  //Create various date attributes from the date column
  //Add DateKey
  DateKey = Table.AddColumn(ChangeDateToDateType, "Key",
    each Number.ToText(Date.Year([Date]))&
         Text.PadStart(Number.ToText(Date.Month([Date])),2,"0")&
         Text.PadStart(Number.ToText(Date.Day([Date])),2,"0")),
  ChangeKeyToNumber = Table.TransformColumnTypes(DateKey,{{"Key", Int64.Type}}),
  MoveKeyToBeginning = Table.ReorderColumns(ChangeKeyToNumber,{"Key", "Date"}),
  // Add Year Column
  Year = Table.AddColumn(MoveKeyToBeginning, "Year",
    each Date.Year([Date])),
    FinancialYear = Table.AddColumn(Year, "FinancialYear", each if Date.Month([Date]) > 6 then Date.Year([Date])+1 else Date.Year([Date])),
  // Add Quarter Column
  Quarter = Table.AddColumn(FinancialYear , "Quarter",
    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),
    FinancialQuarter = Table.AddColumn(Quarter, "FinancialQuarter", each if [Quarter] = "Q1" then "Q3" else if [Quarter] = "Q2" then "Q4" else if [Quarter] = "Q3" then "Q1" else "Q2"),
  // Add Week Number Column
  Week= Table.AddColumn(FinancialQuarter , "Week",
    each Date.WeekOfYear([Date])),
  // Add Month Number Column
  Month = Table.AddColumn(Week, "Month",
    each Date.Month([Date])),
  //Add Month Name Column
  MonthName = Table.AddColumn(Month , "MonthName",
    each Date.ToText([Date],"MMMM")),
  FinancialMonth = Table.AddColumn(MonthName, "FinancialMonth", each if Date.Month([Date]) < 7 then Date.Month([Date]) + 6 else Date.Month([Date]) - 6 ),
  //Add Day of Week Column
  DayOfWeek = Table.AddColumn(FinancialMonth , "Day of Week",
    each Date.ToText([Date],"dddd")),
  // Add YearsAgo
    YearsAgo = Table.AddColumn(
    DayOfWeek,
    "YearsAgo",
    each
      Date.Year(DateTime.LocalNow()) - [Year]
  ),
    MonthsAgo = Table.AddColumn(YearsAgo, "MonthsAgo", each (12*[YearsAgo])+ Date.Month(DateTime.LocalNow()) - [Month]),
    WeeksAgo = Table.AddColumn(MonthsAgo, "WeeksAgo", each (52*[YearsAgo]) + Date.WeekOfYear(DateTime.LocalNow()) - Date.WeekOfYear([Date])),
  // Add DaysAgo
    DaysAgo = Table.AddColumn(
    WeeksAgo,
    "DaysAgo",
    each
      (365*[YearsAgo]) + Date.DayOfYear(DateTime.LocalNow()) - Date.DayOfYear([Date])
  ),
  // FinancialPeriod
  FinancialPeriod = Table.AddColumn(DaysAgo, "FinancialPeriod", each Number.ToText([FinancialYear])&Text.PadStart(Number.ToText([FinancialMonth]),2,"0")),
  ShortMonthName = Table.AddColumn(FinancialPeriod, "ShortMonthName", each Text.Range([MonthName],0,3)),
  IsInCurrentMonth = Table.AddColumn(ShortMonthName, "IsInCurrentMonth", each Date.IsInCurrentMonth([Date])),
  IsInCurrentYear = Table.AddColumn(IsInCurrentMonth, "IsInCurrentYear", each Date.IsInCurrentYear([Date])),
  IsInCurrentFinancialYear = Table.AddColumn(IsInCurrentYear, "IsInCurrentFinancialYear", each if CurrentFinancialYear = [FinancialYear] then true else false),
  MTD = Table.AddColumn(IsInCurrentFinancialYear, "MTD", each if ([IsInCurrentMonth] = true and [DaysAgo] >= 0) then true else false),
  ChangeYearType = Table.TransformColumnTypes(MTD,{{"Year", Int64.Type}, {"FinancialYear", Int64.Type}, {"Week", Int64.Type}, {"Month", Int64.Type}, {"FinancialMonth", Int64.Type}, {"YearsAgo", Int64.Type}, {"MonthsAgo", Int64.Type}, {"WeeksAgo", Int64.Type}, {"DaysAgo", Int64.Type}, {"FinancialPeriod", Int64.Type}, {"Quarter", type text}, {"FinancialQuarter", type text}, {"MonthName", type text}, {"Day of Week", type text}, {"ShortMonthName", type text}, {"IsInCurrentMonth", type logical}, {"IsInCurrentYear", type logical}, {"IsInCurrentFinancialYear", type logical}, {"MTD", type logical}})
in
  ChangeYearType