How to create a Calendar Dates Table

---

Note - This blog is an important Foundational Concept of Power BI

 

Dates =  

 
GENERATE ( 
CALENDAR(DATE(2021,1,1),EOMONTH ( TODAY () , 0 )), 
VAR currentDay = [Date] 
VAR day = DAY( currentDay ) 
VAR dayname = FORMAT([Date],"dddd") 
VAR Weekdaynum = WEEKDAY([Date],2) 
VAR month = MONTH ( currentDay ) 
VAR year = YEAR ( currentDay ) 
VAR monthname = FORMAT([Date],"mmmm") 
VAR week = IF((WEEKNUM([Date],2))<10,"Week 0" & WEEKNUM([Date],2),"Week " & WEEKNUM([Date],2)) 

VAR qtr = "Qtr " & QUARTER([Date]) 
VAR yearmonthnumber = YEAR([Date]) * 100 + MONTH([Date]) 
VAR yearquarternumber = YEAR([Date]) * 100 + QUARTER([Date]) 
VAR yearmonthdatenumber = YEAR([Date]) * 10000 + MONTH([Date])*100 + day ( [Date] ) 

VAR mmmyy = LEFT(monthname, 3) & " " & RIGHT(year, 2) 
VAR qqyy = LEFT(qtr, 5) & " " & RIGHT(year, 2) 

RETURN ROW ( 

"Day", day, 
"Day_Name", dayname, 
"Week_Day_Num", Weekdaynum, 
"Month", month, 
"Month_Name", monthname, 
"Week" , week, 
"Quarter" , qtr, 
"Year", year, 
"Year Month Number", yearmonthnumber, 
"Year Quarter Number", yearquarternumber, 
"Yearmthdt", Yearmonthdatenumber, 
"MMMYY",mmmyy, 
"QQYY",qqyy 
) 
) 

---

Problem

Imagine getting stuck when dealing with dates in Power BI and DAX.

For example,

1. You want to sort by Jan, Feb, Mar

   1. But Power BI can by itself only apply sorting logic by alphabetical i.e., April, August, December

   2. Or sort based on the Value.

2. Imagine you have multiple tables with different dates but eventually it should all be a part of month.

Seems, frustrating right.

Well, just think it like this. Power Bi doesn't know what a Calendar looks like. It doesn't have a deep understanding of year, months, days.

Solution

We make a date column and connect with our Fact Tables

Steps

1. Go to your data model and create a new Calculated table.

2. Then, we will use the CALENDAR DAX function to create a Date table

   1. create months, days and other required columns based on the date column we get from the CALENDAR function.

Additional Solution

1. You can do month sorting by selecting Month Name column.

   1. Click on Sort by Column

   2. Click on Month

Understanding the Formula

1. In the formula above, we took advantage of VAR to create measures within the measure and return the measures we specify.
   

2. In the CALENDAR date function we have specified 2021-1-1 as the start date and the end date of the current month using the EOMONTH function which brings the end date of a specified month, we have used the TODAY() function to specific the current day in the EOMONTH function.
   

3. Instead of using end of the month, we can also put current day’s date as the end date or we can also add extra days by just adding the number of days we want, which would make the calendar function’s specified values look like this CALENDAR(DATE(2021,1,1),TODAY()+30)
   

4. After using the CALENDAR function, we have taken advantage of FORMAT, MONTH, YEAR etc. functions to create a date table that contains almost everything one would require in a Date table.