Excel Magic Trick 1367

Power BI Desktop: DAX CALENDAR Function to Create Calendar / Date Table

DESCRIPTION

How to make Calendar Table in Power BI Desktop?
See how to create three types of Calendar Tables in Power BI Desktop using the CALENDAR Function and various other functions:
(00:25) Introduction
(01:54) Define Calendar / Date Table
(02:50) Calendar Table #1: All Dates between Min and Max dates in the Fact / Sales Table. See the DAX Functions: CALENDAR, MIN and MAX. Learn about the DAX “Create” Table Function: CALENDAR.
(05:08) Calendar Table #2: All Dates between the first day in the first month, and the last day in the last month from the Fact / Sales Table. See the DAX Functions: CALENDAR, MIN, MAX and EOMONTH.
(07:42) Calendar Table #3: All Dates for any year (first of year to end of year) that is represented in the Fact / Sales Table. See the DAX Functions: CALENDAR, MIN, MAX, YEAR and DATE.
(09:36) Create a Year DAX Calculated Column with two methods: 1) YEAR function, 2) Dot Convention DAX Formula that allows us to extract only certain parts of the date, such as the formula: dCalendar[Date].[Year] to get the Year from a Serial Number Date.
(11:26) Create a Month Number DAX Calculated Column using the Dot Convention DAX Formula that allows us to extract only certain parts of the date, such as the formula: dCalendar[Date].[MonthNo] to get the Month Number from a Serial Number Date.
(11:50) Create a Month Name DAX Calculated Column with two methods: 1) FORMAT function, 2) Dot Convention DAX Formula that allows us to extract only certain parts of the date, such as the formula: dCalendar[Date].[Month] to get the Month Name from a Serial Number Date.
(13:55) Create Relationship between Calendar Table and Fact Table.
(14:37) Sort Month Name Column by Month Number Column.
(15:30) Test Calendar Tables by adding new dates to the Fact Sales Table.
(17:03) Summary