How to create a Dynamic Date Calendar using Power Query in Power BI

How to create a Dynamic Date Calendar using M-Query in Power BI

Scenario:

Suppose we want to generate a Calendar Date Table with Dates ranging from Last 5 Years from Today to the Next 5 Years.

We can achieve this Scenario, using the below M-Query(Power Query).
DimCalendar:

let
/* The Calendar Table with Dates from Last 5 Years and Next 5 Years from Today */
vStartDate = Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),-5)),
vEndDate = Date.EndOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),5)),
DatesList = 

    List.Dates(vStartDate, Duration.Days(vEndDate-vStartDate)+1, #duration(1,0,0,0)),
DatesTable = 

    Table.FromList(DatesList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
DimDate = 

    Table.RenameColumns(DatesTable,{{"Column1", "DimDate"}}),
DimYear = 

    Table.AddColumn(DimDate, "DimYear", each Date.Year([DimDate])),
YearStartDate = 

    Table.AddColumn(DimYear, "YearStartDate", each Date.StartOfYear([DimDate])),
YearEndDate = 

    Table.AddColumn(YearStartDate, "YearEndDate", each Date.EndOfYear([DimDate])),
Qtr = Table.AddColumn(YearEndDate, "Qtr", each "Q" &         Number.ToText(Date.QuarterOfYear([DimDate]))),
Qtr_Yr = 

    Table.AddColumn(Qtr, "Qtr_Yr", each [Qtr] & "-" & Number.ToText([DimYear])),
Qtr_StartDate = 

    Table.AddColumn(Qtr_Yr, "Qtr_StartDate", each Date.StartOfQuarter([DimDate])),
Qtr_EndDate = 

    Table.AddColumn(Qtr_StartDate, "Qtr_EndDate", each Date.EndOfQuarter([DimDate])),
MonthNo = 

    Table.AddColumn(Qtr_EndDate, "MonthNo", each Date.Month([DimDate])),
MonthFullName = 

    Table.AddColumn(MonthNo, "MonthFullName", each Date.MonthName([DimDate])),
MonthName = 

    Table.AddColumn(MonthFullName, "MonthName", each Text.Start(Date.MonthName ([DimDate]),3)),
MonthYear = 

Table.AddColumn(MonthName, "MonthYear", each [MonthName] & "-" & Number.ToText([DimYear])),

MonthStartDate = 

    Table.AddColumn(MonthYear, "MonthStartDate", each Date.StartOfMonth([DimDate])),
MonthEndDate = 

    Table.AddColumn(MonthStartDate, "MonthEndDate", each Date.EndOfMonth([DimDate])),
DaysInMonth = 

    Table.AddColumn(MonthEndDate, "DaysInMonth", each Date.DaysInMonth([DimDate])),
WeekOfMonth = 

    Table.AddColumn(DaysInMonth, "WeekOfMonth", each Date.WeekOfMonth([DimDate])),
WeekDayName = 

Table.AddColumn(WeekOfMonth, "WeekDayName", each Text.Start(Date.DayOfWeekName([DimDate]),3)),

WeekOfYear = 

    Table.AddColumn(WeekDayName, "WeekOfYear", each Date.WeekOfYear([DimDate])),
WeekNumYear = 

Table.AddColumn(WeekOfYear, "WeekNumYear", each "Week " & Number.ToText([WeekOfYear])),

WeekDayFullName = 

Table.AddColumn(WeekNumYear, "WeekDayFullName", each Date.DayOfWeekName([DimDate])),

WeekStartDate = 

Table.AddColumn(WeekDayFullName, "WeekStartDate", each Date.StartOfWeek([DimDate])),
WeekEndDate = 

    Table.AddColumn(WeekStartDate, "WeekEndDate", each Date.EndOfWeek([DimDate])),
DaysInWeek = 

Table.AddColumn(WeekEndDate, "DaysInWeek", each Duration.Days([WeekEndDate]-[WeekStartDate])+1),
DayOfWeek = 

    Table.AddColumn(DaysInWeek, "DayOfWeek", each Date.DayOfWeek([DimDate])+1),
DayOfMonth = 

    Table.AddColumn(DayOfWeek, "DayOfMonth", each Date.Day([DimDate])),
DayOfYear = 

    Table.AddColumn(DayOfMonth, "DayOfYear", each Date.DayOfYear([DimDate])),
RefreshDate = 

    Table.AddColumn(DayOfYear, "RefreshDate", each DateTime.LocalNow()),
ChangeType = Table.TransformColumnTypes(RefreshDate,{{"DimDate", type date}, {"DimYear", Int64.Type}, {"YearStartDate", type date}, {"YearEndDate", type date}, {"Qtr", type text}, {"Qtr_Yr", type text}, {"Qtr_StartDate", type date}, {"Qtr_EndDate", type date}, {"MonthNo", Int64.Type}, {"MonthFullName", type text}, {"MonthName", type text}, {"MonthYear", type text}, {"MonthStartDate", type date}, {"MonthEndDate", type date}, {"DaysInMonth", Int64.Type}, {"WeekOfMonth", Int64.Type}, {"WeekDayName", type text}, {"WeekOfYear", Int64.Type}, {"WeekNumYear", type text}, {"WeekDayFullName", type text}, {"WeekStartDate", type date}, {"WeekEndDate", type date}, {"DaysInWeek", Int64.Type}, {"DayOfWeek", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayOfYear", Int64.Type}, {"RefreshDate", type datetime}})
in
    ChangeType

Result:

Notes:
We can use the following expression to create a Sort Key for the Month-Year.

Month_SortKey =

DimCalendar[DimYear]+DimCalendar[MonthNo]+INT(RIGHT(DimCalendar[DimYear],2))*12

We can generate Month Number from Month Name using below logic in Power Query:

MonthNoFromName = Date.Month (Date.FromText ("1"& [MonthName]))

Similarly, We can generate Month Number from Month Name using below logic in DAX:

MonthNoFromName = MONTH(DATEVALUE(1 &"/"& [MonthName] &"/"& 1))

We can generate Month Name from Month Number using below logic in DAX:

MonthNameFromNo = FORMAT(DATE(1,[MonthNo],1),"MMM")

In the above example, I have created the below additional Calculated Columns using the DAX as shown below. These Flag Columns are useful for Calculations and Filters.

ClosedDayFlag =

        IF( DimCalendar[DimDate]<TODAY(), 1,0)

CurMonthFlag =

        IF( AND(YEAR(DimCalendar[DimDate])=YEAR(TODAY()),

                        MONTH(DimCalendar[DimDate])=MONTH(TODAY())), 1,0)

CurYearFlag =

        IF(YEAR(DimCalendar[DimDate])=YEAR(TODAY()),1,0)

YearRangeFlag =

    VAR vThisYear = YEAR(TODAY())

    VAR vDimYear = DimCalendar[DimYear]

    VAR vYearsDiff = vThisYear-vDimYear

RETURN

SWITCH(TRUE(),

    vYearsDiff<0, "CurYear+" & ABS(vYearsDiff),

    vYearsDiff=0, "CurYear",

    vYearsDiff>0, "CurYear-"& vYearsDiff

    )

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------