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

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

Scenario:

Suppose we want to generate a Calendar Date Table with Dates ranging from Last 1 Year to Next 1 Year from Today.

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

let

/* The Calendar Table with Dates from Last 1 Year to Next 1 Year from Today */

_StartDate = Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),-1)),

_EndDate = Date.EndOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),1)),

_DatesList = 

    List.Dates(_StartDate, Duration.Days(_EndDate-_StartDate)+1, #duration(1,0,0,0)),

_DatesTable = 

    Table.FromList(_DatesList, Splitter.SplitByNothing(), {"DimDate"}),

_DimYear = 

    Table.AddColumn(_DatesTable, "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])),

_DayOfMonth = 

    Table.AddColumn(_DayOfWeek, "DayOfMonth", each Date.Day([DimDate])),

_DayOfYear = 

    Table.AddColumn(_DayOfMonth, "DayOfYear", each Date.DayOfYear([DimDate])),

/* Fiscal Year, Quarter, Month and Week */

_FiscalYear = 

    Table.AddColumn(_DayOfYear, "Fiscal_Year", each if Date.Month([DimDate]) >= 4 

    then Date.Year([DimDate]) else Date.Year([DimDate]) - 1),

_FiscalMonth = 

    Table.AddColumn(_FiscalYear, "Fiscal_Month", each if Date.Month([DimDate]) >= 4 

    then Date.Month([DimDate]) - 3 else Date.Month([DimDate]) + 9),

_FiscalQuarter = 

    Table.AddColumn(_FiscalMonth, "Fiscal_Quarter", each "Q" & Number.ToText(Number.RoundUp([Fiscal_Month] / 3))),

_FiscalWeekNo = Table.AddColumn(_FiscalQuarter, "Fiscal_Week_No", each 

    let

        _FiscalStart = #date(if Date.Month([DimDate]) >= 4 then Date.Year([DimDate]) else Date.Year([DimDate]) - 1, 4, 1),

        _WeekNum = Number.RoundDown(Duration.Days([DimDate] - _FiscalStart) / 7) + 1

    in

        _WeekNum

),

_RefreshDate = 

    Table.AddColumn(_FiscalWeekNo, "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}, {"Fiscal_Year", Int64.Type}, {"Fiscal_Month", Int64.Type}, {"Fiscal_Quarter", type text}, {"Fiscal_Week_No", Int64.Type}, {"RefreshDate", type datetime}})

in

    ChangeType

Result:

Notes:

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

Month_SortKey = (dim_Calendar[DimYear]*100) + dim_Calendar[MonthNo]

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

    )

We can also generate the Year Range flag with values as CY-2, CY-1, Current Year(CY), CY+1, CY+2..etc, using the following M-Query logic:

YearRangeFlag =

    Table.AddColumn( ChangType, "Year_Range_Flag", each let     vCurYear = Date.Year(DateTime.LocalNow()),     vYearsDiff = (vCurYear-[DimYear]),

        vYearsDiffText = Number.ToText(vYearsDiff),

        vResult = if vYearsDiff=0 then "CY" else if vYearsDiff<0 then "CY+"& Number.ToText(Number.Abs(vYearsDiff)) else if vYearsDiff>0 then "CY-" & vYearsDiffText else null in vResult)

For the Month Range flag, we can use following M-Query, which generates the values like CM-2, CM-1, Current Month (CM), CM+1, CM+2..etc.

MonthRangeFlag = Table.AddColumn(YearRangeFlag, "Month_Range_Flag", each let vCurYear = Number.ToText(Date.Year(DateTime.LocalNow())), vCurMonth = Number.ToText(Date.Month(DateTime.LocalNow())), vMonthCode = if Text.Length(vCurMonth)<2 then ("0" & vCurMonth) else vCurMonth, vYearMonthCode = Number.FromText(vCurYear & vMonthCode), vMonthsDiff = (vYearMonthCode - [Year_Month_Code]), vResult = if vMonthsDiff=0 then "CM"     else if vMonthsDiff<0 then "CM+"& Number.ToText(Number.Abs(vMonthsDiff))     else if vMonthsDiff>0 then "CM-" & Number.ToText(vMonthsDiff) else null in vResult)

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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