How to make the perfect CALENDAR TABLES πŸ“… in Power BI @chandoo_
How to make the perfect CALENDAR TABLES πŸ“… in Power BI  @chandoo_
Uploaded August 2023 | Updated November 2026, 17 hours ago
CALENDAR Tables are a must if you want to analyze business data in Power BI. In this video, let me show you how I create my calendar tables out of thin air πŸŒͺ



In the video ⏱
============

0:00 - Why you need a calendar table in Power BI?
0:26 - Using "Blank Query" to make calendar table
2:00 - Adding useful date columns (year, month, weekday etc.)
2:28 - Is weekend column
3:00 - Adding "start of the month" calculated column
3:17 - Year month column with Custom Power Query formula
4:08 - Type of the month (this month, previous month, next month, etc.)
7:10 - Loading the calendar table to Power BI

πŸ“ Sample file & code
==================

Get the full M code (Power Query steps) and more from here -
chandoo.org/wp/power-query-calendar-table-best-method

πŸ“Ί WATCH NEXT
==============

Using Power BI to make a dashboard
youtu.be/mXD0Peot4hY

How to use Power BI (10 min intro)
youtu.be/9tF1IrfLflg

Beginner to PRO Power BI Class
youtu.be/uwe8C7K8fXY

Learn Power Query in 15 minutes
youtu.be/UAFExySaSPY

How to create DAX measures
youtu.be/PQ5lALdFHCU

Star Schema in Power BI
youtu.be/1CgG0t59WcM

πŸ’₯FULL Power BI Course
chandoo.org/wp/power-bi-course


πŸ‘©β€πŸ’» Power Query Script for Calendar Table
==================================
Here is the M language script. Paste this in "advanced editor" in Power Query πŸ‘‡

~~~

let
Source = List.Dates(#date(2023,1,1),365, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Is Weekend?", each if [Day of Week] = 6 then "Yes" else if [Day of Week] = 0 then "Yes" else "No"),
#"Inserted Start of Month" = Table.AddColumn(#"Added Conditional Column", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Added Custom" = Table.AddColumn(#"Inserted Start of Month", "Year Month", each [Year] * 100 + [Month]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Current Month", each let cm = Date.StartOfMonth(DateTime.LocalNow())
in Date.Year(cm) * 100 + Date.Month(cm)),
#"Inserted Subtraction" = Table.AddColumn(#"Added Custom1", "Subtraction", each [Year Month] - [Current Month], type number),
#"Added Conditional Column1" = Table.AddColumn(#"Inserted Subtraction", "Month Type", each if [Subtraction] = 0 then "This Month" else if [Subtraction] = 1 then "Next Month" else if [Subtraction] = -1 then "Previous Month" else "Other Month"),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Current Month", "Subtraction"})
in
#"Removed Columns"

~~~



#powerquery #powerbi

~

Why did the Power BI developer never go past first date?
Because he lacked "date intelligence" πŸ˜‚
How to make the perfect CALENDAR TABLES πŸ“… in Power BICreate a HR Dashboard with Tableau - Full TutorialAutomatically Extract BOLD Text with VBA [Adv. Excel]Excel GROUPBY Function is MAGICπŸͺ„How to use Python in Excel - Demo + My ReviewBuilt- in AI features of Excel (no need for ChatGPT)MAX IF Formula HACK to Find TOP Sales in SECONDSIf you work in finance, here are the top 10 Excel functions for you πŸ’ΈSQL Joins - Beginner to PRO Masterclass with 10 ExamplesLET and SCAN Just Did This #excel #formulasFREE Power Query Materclass with 5 Advanced Tips to SPEED UP your work πŸš€How to Self-Join Tables in Power Query [Step-by-step Tutorial]
Chandoo |

How to make the "perfect" CALENDAR TABLES πŸ“… in Power BI

SHARE TO X SHARE TO REDDIT SHARE TO FACEBOOK WALLPAPER