using the Calendar function and power query

[MikeSolvesBI]

Hello,

I know I can create a table using the Calendar function in DAX.

Is there a way that I can then use Power Query to add columns to this table or should I just use Power Query to create the Calendar?

Thanks,

Michael

[CCUK22]

Thank you Mikelytics, I need dim_date and found your script! 

[sevenhills]

In general, Rule of thumb is First Power Query, and then model, and then DAX based fields/measures.

Like you said, we can do Calendar table in power query and DAX. I prefer personally M Query and then model the data types and formats. There are lot of articles on the web on how to create both ways. It gets more interesting when you want to do Fiscal Calendar also. 

To answer about adding columns,

      you can do in power query

      you can do in DAX

However, this is not possible, creating the DAX table first and then adding columns in Power Query. Other way works!

[sevenhills]

References for creating date tables:

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

https://dax.guide/calendar/ 

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ 

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

https://curbal.com/curbal-learning-portal/create-a-custom-calendar-in-power-bi-using-power-query 

https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/

https://gorilla.bi/power-query/date-table/ 

https://radacad.com/power-bi-date-or-calendar-table-best-method-dax-or-power-query

https://www.mssqltips.com/sqlservertip/6756/power-bi-calendar-table/ 

[Mikelytics · Accepted solution]

Hi Michael,

I recommend you to create the table in Power Query.

Try the following script by creating a blank query. Pleas adjust the first two lines (StartDate and EndDate) to your needs directly in the query editor.

let
    StartDate = #date(2020,1,1),
    EndDate = #date(2023,12,31),
    DateList = List.Dates(StartDate, Number.From(EndDate) - Number.From(StartDate), #duration(1, 0, 0, 0)),
    DatesAsTable = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenamedColumnDate = Table.RenameColumns(DatesAsTable, {{"Column1", "PK_Date"}}),
    ChangedTypeDate = Table.TransformColumnTypes(RenamedColumnDate,{{"PK_Date", type date}}),
    Year = Table.AddColumn(ChangedTypeDate, "Year", each Date.Year([PK_Date])),
    QuarterOfYear = Table.AddColumn(Year, "QuarterofYear", each Date.QuarterOfYear([PK_Date])),
    QuarterNameOfYear = Table.AddColumn(QuarterOfYear, "QuarterNameOfYear", each "Q" & Number.ToText([QuarterofYear])),
    QuarterWithYear = Table.AddColumn(QuarterNameOfYear, "QuarterWithYear", each Number.ToText([Year]) & "-" & [QuarterNameOfYear]),
    MonthNum = Table.AddColumn(QuarterWithYear, "MonthNum", each Date.Month([PK_Date])),
    MonthName = Table.AddColumn(MonthNum, "MonthName", each Date.ToText([PK_Date], "MMMM")),
    MonthNameShort = Table.AddColumn(MonthName, "MonthNameShort", each Date.ToText([PK_Date], "MMM")),
    MonthWIthYear = Table.AddColumn(MonthNameShort, "MonthWithYear", each Number.ToText([Year]) & "-" & [MonthNameShort]),
    MonthNameSorting = Table.AddColumn(MonthWIthYear, "MonthNameSorting", each [Year] * 10 + [MonthNum]),
    WeekNumOfYear = Table.AddColumn(MonthNameSorting, "WeekNumOfYear", each Date.WeekOfYear([PK_Date])),
    WeekNameOfYear = Table.AddColumn(WeekNumOfYear, "WeekNameOfYear", each "KW" & Text.PadStart(Number.ToText([WeekNumOfYear]),2,"0")),
    WeekWithYear = Table.AddColumn(WeekNameOfYear, "WeekWithYear", each Number.ToText([Year]) & "-" & [WeekNameOfYear]),
    DayNumOfYear = Table.AddColumn(WeekWithYear, "DayNumOfWeek", each Date.DayOfWeek([PK_Date])+1),
    DayNameOfWeek = Table.AddColumn(DayNumOfYear, "DayNameofWeek", each Text.Start(Date.DayOfWeekName([PK_Date]), 2)),
    ChangeType = Table.TransformColumnTypes(DayNameOfWeek,{{"Year", Int64.Type}, {"QuarterofYear", Int64.Type}, {"MonthNum", Int64.Type}, {"WeekNumOfYear", Int64.Type}, {"QuarterNameOfYear", type text}, {"QuarterWithYear", type text}, {"MonthName", type text}, {"MonthNameShort", type text}, {"MonthWithYear", type text}, {"WeekNameOfYear", type text}, {"DayNameofWeek", type text}, {"DayNumOfWeek", Int64.Type}, {"MonthNameSorting", Int64.Type}})
in
    ChangeType

creat blank query 

adjust parameters

result

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[vijayk416]

Should this not be multiplied by 100 instead of by 10?

MonthNameSorting = Table.AddColumn(MonthWIthYear, "MonthNameSorting", each [Year] * 10 + [MonthNum]),

[Stwalker]

@Mikelytics if I wanted the end date to be now or today, how would that look?