🀄

2. Date and Time Functions

Status

Done

2. Date and Time Functions

DATE, DATEDIFF, DATEVALUE, TIME, TIMEVALUE
YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
NOW, TODAY, WEEKDAY, WEEKNUM, QUARTER, EOMONTH
STARTOFMONTH, ENDOFMONTH, STARTOFQUARTER, ENDOFQUARTER, STARTOFYEAR, ENDOFYEAR
CALENDAR, CALENDARAUTO

TransactionID	TransactionDate	TransactionTime	TransactionDateTime
101	2023-01-15	10:15:00	2023-01-15 10:15:00
102	2023-02-20	12:30:00	2023-02-20 12:30:00
103	2023-03-10	14:45:00	2023-03-10 14:45:00
104	2023-04-25	09:00:00	2023-04-25 09:00:00
105	2023-12-31	18:20:00	2023-12-31 18:20

DAX Date and Time Functions – Full Study Table

Function	Default DAX Formula	Description	Python Expression	DAX Formula Equivalent	Result	Group
DATE	`DATE(...)`	Creates a date from year, month, and day.	`datetime(2023, 4, 25)`	`DATE(2023, 4, 25)`	2023-04-25	Creating Dates and Times
TIME	`TIME(...)`	Creates a time from hour, minute, and second.	`datetime.strptime('12:30:00', '%H:%M:%S').time()`	`TIME(12, 30, 0)`	12:30:00	Creating Dates and Times
DATEVALUE	`DATEVALUE(...)`	Converts a date string to a date.	`pd.to_datetime('2023-03-10')`	`DATEVALUE("2023-03-10")`	2023-03-10	Converting Text to Date or Time
TIMEVALUE	`TIMEVALUE(...)`	Converts a time string to a time.	`pd.to_datetime('14:45:00').time()`	`TIMEVALUE("14:45:00")`	14:45:00	Converting Text to Date or Time
YEAR	`YEAR(...)`	Extracts the year from a date.	`sales['TransactionDate'].dt.year`	`YEAR(Sales[TransactionDate])`	[2023, 2023, 2023, 2023, 2023]	Extracting Date and Time Parts
MONTH	`MONTH(...)`	Extracts the month from a date.	`sales['TransactionDate'].dt.month`	`MONTH(Sales[TransactionDate])`	[1, 2, 3, 4, 12]	Extracting Date and Time Parts
DAY	`DAY(...)`	Extracts the day from a date.	`sales['TransactionDate'].dt.day`	`DAY(Sales[TransactionDate])`	[15, 20, 10, 25, 31]	Extracting Date and Time Parts
HOUR	`HOUR(...)`	Extracts the hour from datetime.	`sales['TransactionDateTime'].dt.hour`	`HOUR(Sales[TransactionDateTime])`	[10, 12, 14, 9, 18]	Extracting Date and Time Parts
MINUTE	`MINUTE(...)`	Extracts the minute from datetime.	`sales['TransactionDateTime'].dt.minute`	`MINUTE(Sales[TransactionDateTime])`	[15, 30, 45, 0, 20]	Extracting Date and Time Parts
SECOND	`SECOND(...)`	Extracts the second from datetime.	`sales['TransactionDateTime'].dt.second`	`SECOND(Sales[TransactionDateTime])`	[0, 0, 0, 0, 0]	Extracting Date and Time Parts
NOW	`NOW(...)`	Returns current datetime.	`datetime.now()`	`NOW()`	Current timestamp	Current Date and Time
TODAY	`TODAY(...)`	Returns current date only.	`datetime.today().date()`	`TODAY()`	Current date	Current Date and Time
UTCNOW	`UTCNOW(...)`	Returns current UTC datetime.	`datetime.utcnow()`	`UTCNOW()`	UTC timestamp	Current Date and Time
UTCTODAY	`UTCTODAY(...)`	Returns current UTC date.	`datetime.utcnow().date()`	`UTCTODAY()`	UTC date	Current Date and Time
WEEKDAY	`WEEKDAY(...)`	Returns day of the week (1=Sun).	`sales['TransactionDate'].dt.weekday + 1`	`WEEKDAY(Sales[TransactionDate], 1)`	[7, 1, 5, 2, 7]	Week, Quarter, and Year Calculations
WEEKNUM	`WEEKNUM(...)`	Returns week number of year.	`sales['TransactionDate'].dt.isocalendar().week`	`WEEKNUM(Sales[TransactionDate])`	[2, 8, 10, 17, 52]	Week, Quarter, and Year Calculations
QUARTER	`QUARTER(...)`	Returns quarter of the year.	`sales['TransactionDate'].dt.quarter`	`QUARTER(Sales[TransactionDate])`	[1, 1, 1, 2, 4]	Week, Quarter, and Year Calculations
DATEDIFF	`DATEDIFF(...)`	Number of days between two dates.	`(sales['TransactionDate'][4] - sales['TransactionDate'][0]).days`	`DATEDIFF(Sales[TransactionDate][0], Sales[TransactionDate][4], DAY)`	350	Date Differences
EOMONTH	`EOMONTH(...)`	End of the same month.	`sales['TransactionDate'] + pd.offsets.MonthEnd(0)`	`EOMONTH(Sales[TransactionDate], 0)`	[2023-01-31, ..., 2023-12-31]	Start and End of Periods
STARTOFMONTH	`STARTOFMONTH(...)`	Start of month.	`sales['TransactionDate'].values.astype('datetime64[M]')`	`STARTOFMONTH(Sales[TransactionDate])`	[2023-01-01, ..., 2023-12-01]	Start and End of Periods
ENDOFMONTH	`ENDOFMONTH(...)`	End of month.	Same as EOMONTH	`ENDOFMONTH(Sales[TransactionDate])`	Same as EOMONTH	Start and End of Periods
CALENDAR	`CALENDAR(...)`	Generates date range table.	`pd.date_range('2023-01-01', '2023-01-10')`	`CALENDAR(DATE(2023,1,1), DATE(2023,1,10))`	2023-01-01 to 2023-01-10	Calendar Creation
CALENDARAUTO	`CALENDARAUTO(...)`	Auto generates calendar table.	`pd.date_range(sales['TransactionDate'].min(), sales['TransactionDate'].max())`	`CALENDARAUTO()`	2023-01-15 to 2023-12-31	Calendar Creation