There are a few ways to create date table in Power BI. Often, we need to create a few new columns to help with visual sorting etc.
In Model View, click New Table and then using one of the following DAX functions to create the table and other computed columns as needed.
Using DAX:
Calendar = CALENDARAUTO() //returns a contiguous, complete range of dates that are automatically determined from your dataset
Calendar = CALENDAR(date(2010,1,1),date(2050,12,31)) //returns a contiguous range of dates based on a start and end date that are entered as arguments in the function
Calendar = CALENDAR(MIN('table'[date]),MAX('table'[date])) //similar to CALENDARAUTO()
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")
FYMonthNum =
VAR FYStartMonth = 7
//Update the fiscal year starting month above *Use number between 1 to 12
RETURN
IF (
MONTH ( 'Calendar'[Date] ) >= FYStartMonth,
MONTH ('Calendar'[Date] ) - ( FYStartMonth - 1 ),
12 + ( MONTH ('Calendar'[Date] )- ( FYStartMonth - 1 ))
)
FYYear =
VAR FYStartMonth = 7
//Update the fiscal year starting month above *Use number between 1 to 12
RETURN
IF (
MONTH ( 'Calendar'[Date] ) >= FYStartMonth,
YEAR ('Calendar'[Date] ) +1,
YEAR('Calendar'[Date] )
)
Month = FORMAT([Date], "MMM")
Year = YEAR('Calendar'[Date])
Using Power Query (MDX) to generate a list and then use Transform to Convert the list To Table and format the column as Date.
= List.Dates(
#date(2011,05,31) //start date
, 365*10 //dates for every day for the next 10 years
, #duration(1,0,0,0) //specifies duration of the period 1=days, 0=hours, 0=minutes, 0=seconds
)
After the date table is created, in Fields pane, right-click and select Mark as date table.
Select “Mark as date table” will remove autogenerated hierarchies from the date field in the table. Also, autogenerated dates hierarchies on any table will be removed when the relationship is estabalished with the date table.
a common data table with FY:
dimDate =
var _MinYear = YEAR(MIN(Sales[OrderDate]))
var _MaxYear = YEAR(MAX(Sales[OrderDate]))
var _Dates = CALENDAR(DATE(_MinYear,1,1),DATE(_MaxYear,12,31))
var _StartWeek1 = DATE(_MinYear,1,1)
var _FYStartMonth = 7
RETURN
ADDCOLUMNS(
_Dates,
"Year", YEAR([Date]),
"Quarter", YEAR([Date]) & " Q"&QUARTER([Date]), --prefix with YEAR in case source data spand more than one year
"QuarterNo", YEAR([Date]) & QUARTER([Date]), --prefix with YEAR in case source data spand more than one year
"Month", FORMAT([Date],"MMM"),
"MonthNo", MONTH([Date]),
"Week", "W"&WEEKNUM([Date]),
"WeekNo", WEEKNUM([Date]),
"WeekID", WEEKNUM([Date],1) + (YEAR([Date]) - _MinYear) * 53,
"WeekDay", FORMAT([Date], "DDD"),
"DayNo", DAY([Date]),
"DayoftheWeek", FORMAT([Date],"DDDD"),
"FYMonthNum", IF(
MONTH([Date]) >= _FYStartMonth,
MONTH([Date]) - (_FYStartMonth - 1),
12 + (MONTH([Date]) - (_FYStartMonth - 1))
),
"FYYear", IF(
MONTH([Date]) >= _FYStartMonth,
YEAR([Date]) + 1,
YEAR([Date])
)
)