Microsoft Office Tutorials and References
In Depth Information
WEEKDAY, WEEKNUM, YEAR, and YEARFRAC. To make more complex operation on dates,
such as comparing aggregated values year over year or calculating year to date value of a
measure, there is another set of functions called Time Intelligence Functions that will be
described later in the topic in Chapter 7.
As we said before in this chapter, a datetime data type internally uses a floating point number
wherein the integer part corresponds to the day of the month (starting from December 30,
1899) and the decimal part indicates the fraction of the day in time. (Hours, minutes, and
seconds are converted into a decimal fractions of the day.) So adding an integer number to
a datetime value increments the value by a corresponding amount of days. However, most
of the time the conversion functions are used to extract day, month, and year from a date. In
the following example, you can see how to extract this information from a table containing a
list of dates (see Figure 3-25).
Day = DAY( Calendar[Date] )
Month = FORMAT( Calendar[Date], "MM - mmmm" )
Year = YEAR( Calendar[Date] )
FIguRE 3-25 Extracting date information using date and time functions.
As you can see in Figure 3-25, the Month column is calculated using the FORMAT function,
which is classified as a text function but is very useful for building a string that keeps the
right sort order of the months by placing the month number before the month name. (Day
and Year columns are sorted in the right order because of their numeric data type.)
Summary
In this chapter, you have explored the syntax of DAX, its data types, and the operators and
functions available. You have also learned the following:
Differences between calculated columns and measures and how to choose between them
How to handle errors and empty values in DAX expressions using common patterns
What groups of functions are available in DAX (Statistical, Logical, Information,
Mathematical, Text, Date, and Time) and their similarities and differences with
Excel formulas
Search JabSto ::




Custom Search