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
https://chandoo.org/wp/powerqueryca...

WATCH NEXT
==============

Using Power BI to make a dashboard
   • How to Make a Sales Dashboard in Powe...  

How to use Power BI (10 min intro)
   • Your first 10 minutes of Power BI  A...  

Beginner to PRO Power BI Class
   • Beginner to PRO Data Analysis with Po...  

Learn Power Query in 15 minutes
   • Learn Power Query & Automate Boring D...  

How to create DAX measures
   • Learn Power Pivot & DAX for Power BI ...  

Star Schema in Power BI
   • How to setup a Star Schema Data Model...  

FULL Power BI Course
https://chandoo.org/wp/powerbicourse/


‍ 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"