Select Page

In order to perform time-based analysis in your Power BI model, you need a separate calendar table. If you have one available in your source, Plan A is to bring that in and use it in your model. But if there isn't one available, Plan B is to create one from scratch in a DAX Calculated Table. In this formula, the beginning and end of the calendar are based upon the fact table making this a dynamic calendar table. Here is the code for a basic calendar table, you can can adjust it any way you see fit of course:

 

 

dim_Calender =
ADDCOLUMNS (
CALENDAR (
MIN ( fact_Sales[Date] ),
MAX ( fact_Sales[Date] )
),
"Month" , MONTH ( [Date] ),
"MonthName" , FORMAT ( [Date] , "mmmm" ),
"Year" , YEAR ( [Date] ),
"Quarter" , QUARTER ( [Date] ),
"YearMonth" , YEAR ( [Date] ) * 100 + MONTH ( [Date] )
)