Scribd
Upload
105 views

DAX Functions - Date - Time

DAX provides functions in several categories including date/time, logical, text, math/statistical, and filters. Common date functions include YEAR, MONTH, DAY, WEEKDAY, FORMAT. Calculated columns can be created from date fields. A date dimension table containing derived fields like day, month, quarter can be created using the CALENDAR function and used for time series analysis. The EOMONTH function returns the last date of the month based on a start date and number of months.

Uploaded by

anand003
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
105 views

DAX Functions - Date - Time

DAX provides functions in several categories including date/time, logical, text, math/statistical, and filters. Common date functions include YEAR, MONTH, DAY, WEEKDAY, FORMAT. Calculated columns can be created from date fields. A date dimension table containing derived fields like day, month, quarter can be created using the CALENDAR function and used for time series analysis. The EOMONTH function returns the last date of the month based on a start date and number of months.

Uploaded by

anand003
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

DAX Functions

Functions in DAX / DAX Functions Categories

DAX provides the following types or Categories of functions which we mostly used.

 Date and Time Functions


 Logical Functions
 Text Functions
 Math & Statistical Functions
 Filter Functions
 Time Intelligence Functions

Basic Date & Time Functions

Mostly we used Date and Time functions to create Calculated Columns / New Columns.
Let’s us discuss about below date functions.

 YEAR
 MONTH
 DAY
 WEEKDAY
 WEEKNUM
 FORMAT (Text Function)  Month Name, Weekday Name
 DATE
 TODAY
 NOW
 HOUR
 MINUTE
 SECOND
 TIME
 DATEDIFF
 CALENDAR
 EOMONTH
 STARTOFMONTH
Creating Calculated Columns form Date Fields

Here we will create fields for Year, Month and Day.

a) Year = Year (Orders [Order Date])


b) Month = Month (Orders [Order Date])
c) Day = Day (Orders [Order Date])
d) Week Day = Weekday (Orders [Order Date])
e) WeekNum = WEEKNUM (Orders [Order Date])

Create the Month Name field

Month Name = Format (Orders [Order Date],"MMMM")  Gives full Month Name
Month Name = Format (Orders [Order Date],"MMM")  Gives Month Name abbreviations

Create a Weekday Name field

Weekday Name = FORMAT (Orders [Order Date],"DDDD")  Gives Full Weekday Name
Weekday Name = FORMAT (Orders [Order Date],"DDD")  Gives Weekday Name abbreviations

DATEDIFF

No of Days = DATEDIFF (Orders [Order Date], Orders [Ship Date], DAY)


(Select don’t summarize for No of Days to get correct results)

Calculated Tables

In a calculated table, the table values are generated by Data Analysis Expression (DAX)
and the values are stored in the Power BI model. Usually using Calculated Tables, we will
create Date Dimension table and use this table in the model for time series analysis.

Date Dimension Table

Every Time creating this date fields for different data sets is difficult, so what we do is
we create a Date Dimension Table which contains all these fields and use it.

DAX CALENDAR Function

The calendar function returns a table with a single column that contains a continuous
set of dates. The start and end date range will be supplied as parameters.
The following formula returns a calculated table Date_Dim with dates between January 1st,
2011 and December 31st, 2020.

Date_Dim = CALENDAR (DATE (2011, 1, 1), DATE (2020, 12, 31))


From the above Date Column, we will be deriving below columns.

 Day = DAY(Date_Dim[Date])
 WeekDay = WEEKDAY(Date_Dim[Date])
 WeekDayName = FORMAT(Date_Dim[Date],"DDDD")
 Month = MONTH(Date_Dim[Date])
 MonthName = FORMAT(Date_Dim[Date],"MMMM")
 Quarter = ROUNDUP((Date_Dim[Month]/3),0)
 Quarter = IF (Month Number<4,"Qtr 1”, IF (Month Number<7,"Qtr 2”, IF (Month
Number<10,"Qtr 3","Qtr 4")))
 QuarterName = "Qtr" & Date_Dim[Quarter]
 QuarterName = CONCATENATE ("Qtr", Date_Dim[Quarter])
 Year = YEAR(Date_Dim[Date])
 WeekNumber = WEEKNUM(Date_Dim[Date])
 WeekNumMonth = 1 + WEEKNUM (Dim_Date[Date]) -
WEEKNUM(STARTOFMONTH(Dim_Date[Date]))
 WeekNameMonth = "Week" & " " & Date_Dim[WeekNumMonth]

Week of Month

The week number of the month is, one plus the difference between the weeknum for
the date and the weeknum of the first of the month.

WeekOfMonth =1+ WEEKNUM (Dim_Date[Date]) - WEEKNUM(STARTOFMONTH(Dim_Date[Date]))

EOMONTH

EOMONTH Returns the date in datetime format of the last day of the month, before or after a
specified number of months. Use EOMONTH to calculate maturity dates or due dates that fall
on the last day of the month.

Syntax

EOMONTH (<start_date>, <months>)

Example

1. If customer taken a loan for 12 months, to find when the loan will complete use below
syntax.

EOM = EOMONTH (Orders [Order Date], 12)

2. Based on Order Date filed in Orders table, get the Number of Days in that month.
No of Days in Month = DAY ((EOMONTH (Orders [Order Date], 12)))

You might also like