Power BI Date and time functions are crucial components of data analysis and reporting, providing valuable insights into trends, patterns, and time-based metrics. Power BI, a powerful business intelligence tool, offers a range of date and time functions to efficiently handle temporal data and extract meaningful information.
| Function | Description |
| CALENDAR | Returns a table with a single column named “Date” |
| CALENDARAUTO | Returns a table with a single column named “Date” |
| DATE | Returns the specified date |
| DATEDIFF | Returns the number of interval |
| WEEKDAY | Returns a number from 1 to 7 identifying the day of the week of a date. |
| WEEKNUM | Returns the week number |
| YEAR | Returns the year of a date |
| QUARTER | Returns the quarter as a number from 1 to 4. |
| MONTH | Returns the month as a number |
| NOW | Returns the current date and time |
| TIME | Converts hours, minutes, and seconds |
Syntax
Most commonly used Date functions
- CALENDAR(<start_date>, <end_date>)[Text Wrapping Break]
- CALENDARAUTO([fiscal_year_end_month])
- DATE(<year>, <month>, <day>)
- DATEDIFF(<Date1>, <Date2>, <Interval>)
- WEEKDAY(<date>, <return_type>)
- WEEKNUM(<date>[, <return_type>])
- YEAR(<date>)
- QUARTER(<date>)
- MONTH(<datetime>)
- NOW()
- TIME(hour, minute, second)
Examples:
Dim_calendar = CALENDAR
(“2006/01/01” , TODAY() )
Day = DAY(‘Dim_calendar'[Date])
Day_name = FORMAT(‘Dim_calendar'[Date], “dddd” )
Month = MONTH(‘Dim_calendar'[Date])
Month_Name = FORMAT(‘Dim_calendar'[Date], “mmmm”)
Quarter = QUARTER(Dim_calendar[Date])
Week_Day = WEEKDAY(‘Dim_calendar'[Date])
Year = YEAR(‘Dim_calendar'[Date])
