How to make the "perfect" CALENDAR TABLES 📅 in Power BI
Vložit
- čas přidán 30. 07. 2024
- 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-ca...
📺 WATCH NEXT
==============
Using Power BI to make a dashboard
• How to Make a Sales Da...
How to use Power BI (10 min intro)
• Your first 10 minutes ...
Beginner to PRO Power BI Class
• Beginner to PRO Data A...
Learn Power Query in 15 minutes
• Learn Power Query & Au...
How to create DAX measures
• Learn Power Pivot & DA...
Star Schema in Power BI
• How to setup a Star Sc...
💥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" 😂 - Věda a technologie
Your channel is all about straightforward Excel and Power BI content-no fluff, just useful insights!
Can you create scenario based advanced level questions on Excel and Power BI for interviews also please 🌸
I can't imagine how anyone figures this out on their own. Glad I could just copy want you did
I love the "Tadaaaa" in the end... great calendar!
Thank you Chandoo!
Interesting. Thanks Chandoo.
Really helpful, thanks Chandoo!
Thank you so much for this video! Im officially subscribed
Its beauty, I can imagine how searches I used to do to get one that we need, but now we have this great video. Thank you... bunny jumping
Quite helpful.. thanks 😊 for this...
Great video, thank you so much
Thanks for sharing - but does you first calculation take into account leap years?
i should i connect this one to my data, assuming its calendar year, should i do the modeling? how about for FY?
This video helped a lot, thank you Sir
Gorgeous explanation!! Thank You Chandoo!!! Just a small question: I need to create a custom collumn that should give me a "week number", considering always the week starting on Saturday. Is it possible? How can I create it??? Thank so much!!!
really helpful. thank u
Increibly useful my friend. Regards from Argentina
Thank you for this. How would I add a column for the first week day of the month , or the first Monday?
As an example, let's say I work in forecasting and I need to know the first working day of the month
Good question. You can use =Date.AddDays([Start of Month], Date.DayOfWeek([Start of Month],Day.Monday)) with our [Start of the Month] column to get "First Monday of the Month"
@@chandoo_ thanks you much for this vedio this is very usefull .
But I have one doubt SQL skills are same for both financial analysis and data analysis or different can u plzzz clear my doubt iam was in confusion @chandoo_
Hey chandoo, great job man , please tell me, in your opinion wich is best in excel, having all the data in a single workbook, with multiple sheets, or multiple workbooks, from your experience. Thanks man, an keep doing what you are doing.
Hi Chandu, will it possible to add ISO week on same table?
Companies are looking for data breakup in week-wise also
Same question here😅
Thanks for sharing
Always excellent content!
Amazing video, any chance you can show how to include weekly i.e week 1 of Jan
POwerfull Concepts using POwer Query.
6:58 you forgot to tell about the condition for "Last year same month", please advise
Hi Chandoo... this is the perfect calendar in power BI,
But hope you can help me also create columns for "this week", "last week", next week" columns
Similar to what you have done to the months
Thank you very much
Quick question: As a data analyst if i can do everything with power query why do i have to learn SQL?
Data scrapping from Servers
If possible.... Pls. create a video tutorial to show the Fiscal Year calendar using DAX and Power Query. Also pls. show how to derive Week Number, Month Number, Month Name, Quarter number and FY year as 22-23, 23-24 etc.
Hey... Great way of teaching. Can you please tell is this calendar is rolling calendar?
Thank you.
It is not a "rolling calendar", but you can change the "source" step to make it rolling (or any year).
@@chandoo_ how
great video
Great one Boss
Why not used calenderauto dax
how i get 12 month and 12 month number
great chandooo🥰🥰 if we want for 5 or six years?? multiply 365 by nmb of years??
Yes, but to be safe, you want to calculate the exact number of days (as some of those years would be leap).
👏💪
please make media analytics tutorial
Oh. You're storing data on Excel spreadsheets? Well, that certainly explains why you would want a bunch of consecutive numbers. I've mostly just done random things like a small Battleship game and Minesweeper using VBA. I mostly just avoid the formulas altogether...
Make a video on python in excel.
good boy
Me, i have a ready m code for calendar table i sourced from the internet. Ahahahaha
Then you can share here...hahaha
Oh Geezus...this are the guys the government hires to help with quantitative easing and tinkering with the economy....since regular peeps get lost at step 3
That’s too many transformations
Sorry man, this is soooooo complicated - try this instead (just replace the column in the first variable). As this provides also the ISOWEEK - actually I do not have the nonISOWEEK included - but you see it is easy to replace. ANything missing can be added easily.
dimDate =
VAR _DateColumn =
data[datecolumn]
VAR _MinYear =
YEAR(MIN(_DateColumn))
VAR _MaxYear =
YEAR(MAX(_DateColumn))
VAR _MinMonth =
MONTH(MIN(_DateColumn))
VAR _MaxMonth =
MONTH(MAX(_DateColumn))
VAR _MinDay =
DAY(MIN(_DateColumn))
VAR _MaxDay =
DAY(MAX(_DateColumn))
VAR _Dates =
CALENDAR(
DATE(_MinYear, _MinMonth, _MinDay),
DATE(_MaxYear, _MaxMonth, _MaxDay)
)
VAR _StartWeek1 =
DATE(_MinYear, _MinMonth, _MinDay)
RETURN
ADDCOLUMNS(
_Dates,
"Year", YEAR([Date]),
"Month Nr", MONTH([Date]),
"Month", FORMAT([Date], "MMM"),
"Weekday Nr", WEEKDAY([Date], 2),
"Weekday", FORMAT([Date], "DDD"),
"Week ISO", WEEKNUM([Date], 21),
"Year ISO",
IF(
WEEKNUM([Date], 21) < 5 && WEEKNUM([Date]) >50,
YEAR([Date]) + 1,
IF(
WEEKNUM([Date], 21) > 50 && WEEKNUM([Date]) < 5,
YEAR([Date]) - 1,
YEAR([Date])
)
),
"WeekID ISO", ([Date] - MOD([Date], 7) - _StartWeek1 + 13) / 7
)
I disagree. I prefer using PQ or something upstream (like a warehouse) to make my calendar tables.
@@chandoo_ understand your point, but what is the benefit of PQ in this case? The DAX version I can just copy anytime I do a new report and use it over and over and it took a like 3 seconds.
@himanisakhare6326
1 second ago
hello sir. how are you doing ? I hope you are doing well ! ITS JUST AN REQUEST THAT PLEASE MAKE AN VIDEO ON WHAT PARTICULAR TOPIC ONE SHOULD LEARN AND WHAT TOPICS WE SHOULD LEARN FIRST AND WHAT NEXT IN POWER BI FROM A COMPLETE BEGINEER TO KIND OF KNOWLEGEABLE PERSON WHO KNOWS QUIET WELL ABOUT POWER BI, I AM AT SOMEWHERE IN a INTERMEDIATE STAGE BUT STILL CONFUSE about HOW TO APPROACH THE ROADMAP TO LEARN THE POWER BI AND POWER BI SERVICES