How to create an additional DATE table in Power BI Desktop? | Create a Calendar table in Power BI🖥️
1) In this video, we will first understand the need to create a Date Table.
2) In the DAX query view we'll tryout the code and then we will add a few columns to the calendar table
We will use UFO Sightings dataset from Tidytuesday R Repository
Dataset:
https://github.com/rfordatascience/ti...
More datasets @Tidytuesday R
https://github.com/rfordatascience/ti...
After creating Calendar_table, change it into a Date table and
also sort the respective columns
Here is the script for creating Calendar_table
(replace yourTable with name of your table)
Calendar_table =
ADDCOLUMNS(
CALENDAR(
MIN(yourTable[Date]),MAX(yourTable[Date] ) ),
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month_Name", FORMAT ( [Date], "mmmm" ),
"Month-Year Number", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
"Month-Year", FORMAT ( [Date], "mmmm yyyy" ),
"Day of the Week Name", FORMAT([Date],"dddd"),
"weekday ", WEEKDAY([Date]),
"Day", DAY([Date])
)
TIMESTAMPS
00:00 What is the need for a Date Table?
00:51 How to add new columns to the calendar table?
01:30 Using Dax query view, learn about the logic of creating new columns in the calendar table
03:48 Add new columns to the calendar table
04:19 In the table view, sort one column by another column