Data Analysis with Microsoft Power BI

Module 6 – Create Model Calculations using DAX

Institute of Advanced Technologies (IAT)

Create Model Calculations using DAX

Introduction to DAX Real-time Dashboards Advanced DAX

What is DAX? Understanding Context Semi-additive Measures

Measures The CALCULATE function Time-Intelligence

Calculated columns The USERELATIONSHIP
function
Columns vs measures

2
What is DAX?

• Data Analysis Expressions (DAX) is a programming language that is used

throughout Microsoft Power BI for creating calculated columns, measures,
and custom tables.
• It is a collection of functions, operators, and constants that can be used in a
formula, or expression, to calculate and return one or more values.
• You can use DAX to solve a number of calculations and data analysis
problems, which can help you create new information from data that is
already in your model.

3
 Calculated columns

DAX allows you by creating a calculated column that didn't originally

exist in the data source.

For example, assume that you are importing data from a database that
contains sales transactions. Each individual sales transaction has the
following columns: Order ID, Product ID, Quantity, and Unit Price.

Notice that a column doesn't exist for the total sales amount for each
order.

The following figure shows how the initial shape of the data appears in a
Power BI table visual.
 Total Price = 'Sales OrderDetails'[Quantity] *
'Sales OrderDetails'[Unit Price]

 Create the new column by selecting the ellipsis (...) button on

the table in the Fields list and then selecting New column.

 A new DAX formula appears in the formula bar

underneath the ribbon at the top.
 You can replace the “Column =” default text with Total
Price and the following example text:

 Calculated columns are materialized in the .pbix Power BI file extension, meaning that
each time you add a calculated column, you are increasing the size of the overall file.
 Having too many calculated columns will slow performance and will cause you to reach
the maximum Power BI data size sooner.
 Example: Calculated columns

• Let’s go to Power BI Desktop, load DimProduct, DimProductCategory, DimProductSubcategory

and FactInternalSales tables from PowerBIData excel workbook.
• In the Data view, go to a New column for FactInternalSales and create a new column named

SalesAmountUSD and calculate the SalesAmount * 1.6 as shown in figure.

• If you want to add it, you can click 

 If you want to specify category of currency, choose $ English(United States) from $ menu.
 You can check report view with calculated column
 Basic Operators , Logical Operators and Other functions

Arithmetic operator Meaning Example

+ (plus sign) Addition 3+3

– (minus sign) Subtraction or sign 3–1–1
* (asterisk) Multiplication 3*3
/ (forward slash) Division 3/3
^ (caret) Exponentiation 16^4
Comparison operator Meaning Example
= Equal to [Region] = "USA"
== Strict equal to [Region] == "USA"
> Greater than [Sales Date] > "Jan 2009"
< Less than [Sales Date] < "Jan 1 2009"
>= Greater than or equal to [Amount] >= 20000

<= Less than or equal to [Amount] <= 100

<> Not equal to [Region] <> "USA"
 Basic Operators , Logical Operators and Other functions
Text concatenation operator Meaning Example
& (ampersand) Connects, or concatenates, two values to produce one [Region] & ", " & [City]
continuous text value
Logical operator Meaning Example

&& (double ampersand) Creates an AND condition between two expressions that each ([Region] = "France") && ([BikeBuyer] = "yes"))
have a Boolean result.
If both expressions return TRUE, the combination of the
expressions also returns TRUE; otherwise the combination
returns FALSE.

| (double pipe symbol) Creates an OR condition between two logical expressions. If (([Region] = "France") || ([BikeBuyer] = "yes"))
either expression returns TRUE, the result is TRUE; only when
both expressions are FALSE is the result FALSE.

IN Creates a logical OR condition between each row being 'Product'[Color] IN { "Red", "Blue", "Black" }
compared to a table. Note: the table constructor syntax uses
curly braces.
 Basic Operators , Logical Operators and Other functions

Function Description Example

BLANK() Test whether the value is blank = IF( SUM(InternetSales_USD[SalesAmount_USD])= 0 , BLANK() ,
SUM(ResellerSales_USD[SalesAmount_USD])/SUM(InternetSales_USD[SalesAmount_USD]) )

ISBLANK(<value>) Checks whether a value is = IF( ISBLANK('CalculatedMeasures'[PreviousYearTotalSales]) , BLANK() ,

blank, and returns TRUE or ( 'CalculatedMeasures'[Total Sales]-'CalculatedMeasures'[PreviousYearTotalSales] )
FALSE. /'CalculatedMeasures'[PreviousYearTotalSales])

ERROR(<text>) Raises an error with an error DEFINE

message. MEASURE DimProduct[Measure] =
IF(
SELECTEDVALUE(DimProduct[Color]) = "Red",
ERROR("red color encountered"),
SELECTEDVALUE(DimProduct[Color])
)
EVALUATE SUMMARIZECOLUMNS(DimProduct[Color], "Measure", [Measure])
ORDER BY [Color]
 Basic Operators , Logical Operators and Other functions

Logical Function Description Example

SWITCH(<expression>, <value>, Evaluates an expression against = SWITCH([Month], 1, "January", 2, "February", 3, "March", 4,

<result>[, <value>, <result>]…[, a list of values and returns one "April" , 5, "May", 6, "June", 7, "July", 8, "August" , 9, "September",
<else>]) of multiple possible result 10, "October", 11, "November", 12, "December" , "Unknown
expressions. month number" )

IF(<logical_test>, Checks a condition, and returns Price Group =

<value_if_true>[, one value when it's TRUE, IF( 'Product'[List Price] < 500, "Low"
<value_if_false>]) otherwise it returns a second )
value.
 Practice Activity 6-1
• Let's practice these initial functions. We will be using the Model that you developed in the previous
Practice Activity. 4-1
1. In the table DimCustomer, can you create a new calculated column called FullName which
combines Title, FirstName, MiddleName and LastName into one column.
• You may notice some unnecessary spaces. We will be removing them in a later Practice Activity.
2. Create a new calculated column called NoMiddleName which shows "No middle name" if there is
no MiddleName, and a blank if there is a MiddleName.
3. Create a new calculated column called HasBothHouseAndCar.
It should have "Yes" if both HouseOwnerFlag and NumberCarsOwned are at least 1 each. Use the
function AND( , )
If not, then you can either do:
a. "No", or
b. If you are up for a challenge, it should have the values "Car Only", "House Only", and
"Neither", depending on the values of these fields.
4. In the table FactInternetSales, can you create a new calculated column called QuarterNumber which gives
the QuarterNr of the OrderDate.
5. Please create a visualization which shows whether there are any seasonality in the sales, by QuarterNumber
and SalesAmount.
6. Using this calculated column, can you create a new calculated column called Season so that it shows the
words Spring, Summer, Autumn and Winter for the numbers 1, 2, 3 and 4 (or you can use 3, 4, 1, 2, if you are in
the southern hemisphere!). Use the SWITCH function.
7. Update this visualization so that it shows the words Spring, Summer, Autumn and Winter. Sort them by the
Season field. If they are in the wrong order, then sort this field using the QuarterNumber field.
8. Create table visualization which has SalesTerritoryGroup, SalesTerritoryCountry, SalesTerritoryRegion and
Sum of SalesAmount.
9. In the DimSalesTerritory table, create a new calculated column called InUS. It should have the values "In
US" or "Outside of US", depending on whether the SalesTerritoryCountry field says "United Sales" or not.
10. Change the visualization to a Stacked Column Chart, move any Legend and "Small multiples" fields to the
Axis, and add the "InUS" field into the Legend.
Please save the Model developed in this Practice Activity. We will be using it in later Practice Activities.
 Practice Activity 6-1 Solution
Look at the Calculated Column “ FullName” in following figure.
Let’s look at how to add calculated column “ NoMiddleName” in the following figure.
HasBothHouseAndCar = if(AND(DimCustomer[HouseOwnerFlag]>=1,DimCustomer[NumberCarsOwned]>=1),"Yes",
if(DimCustomer[HouseOwnerFlag]>=1,"House Only", if(DimCustomer[NumberCarsOwned]>=1,"Car
Only","Neither")))
You can use the Matrix visual type and the format of visual like this:
Rows : QuarterNumber
Values: SalesAmount
Season =
SWITCH(FactInternetSales[QuarterNumber],1,"Spring",2,"Summer",
3,"Autum",4,"Winter","Error")
• Visual type: Stacked Column
• X axis: SalesTerritoryGroup,
SalesTerritoryCountry,
SalesTerritoryRegion

• Legend: InUS

• Y-axis: Sum of SalesAmount

 Measures
• Use measures
 Calculated columns are useful, but you are required to operate row by row.
 For example, consider a situation where you want an aggregation that operates over the entire dataset
and you want the total sales of all rows.

 Furthermore, you want to slice and dice that data by other criteria like total sales by year, by employee,
or by product.

 You can build a measure without writing DAX code; Power BI will write it for you when you create a quick
measure.

 Many available categories of calculations and ways to modify each calculation exist to fit your needs.
Create a quick measure

• To create a quick measure in Power BI Desktop, right-click or select the ellipsis (...) button next to any item in
the Fields pane and then select New quick measure from the menu that appears.

• The Quick measures screen will appear.

 In the Quick measures window, you can select the calculation that
you want and the fields to run the calculation against.
 For instance, you can select a calculation and the column that you
want to operate over.
 Power BI creates the DAX measure for you and displays the DAX.
 This approach can be a helpful way to learn the DAX syntax.
 For more information, see the Use quick measures for common
calculations documentation.
 Example: Create a quick measure

If you want to add quick measure for your visualization, use quick measure. In the example, SalesVolume depends
on date.
In the calculation dialog box, select Time intelligence with Year-to-date total option.
Base value: Sum of Sales Volume , Date: Date
 Create a measure
• Measures are used in some of the most common data analyses.

• To continue with the previous scenario, you want to create a measure that • When you drag Total Sales

totals your new column for the entire dataset. over to the report design
surface, you will see the total
• Similar to how you created a calculated column, you can go to
sales for the entire organization
the Fields list and select New measure.
in a column chart.
• Text will now appear in the formula bar underneath the ribbon.

• You can replace the “Measure =” text with the following text:
Total Sales = sum('Sales OrderDetails'[Total Price])
Example: Creating Measure

Aggregation calculation
function:
• SUM
• SUMX
• COUNT
• COUNTA
• COUNTAX
• COUNTBLANK
• AVERAGE
• AVERAGEX
• MAX
• MAXA
• MAXX
• MEDIANX
• MIN
• MINA
• MINX
 Example: SUM() vs. SUMX() function

• SUM() operates over a single column and has no awareness of individual rows in the
column (no row by row evaluation).
• SUMX() can operate on multiple columns in a table and can complete row by row
evaluation in those columns.
 Practice Activity 6-2

First of all, let's create a series of calculations using helper columns. Then, we'll recreate this
calculation as a measure, without using any of the helper columns.
Finally, we'll have a look at the [Link] function.
1. Open up the model that you have created in previous Practice Activities.
2. Go to the DimCustomer table.
3. Please create a Calculated Column called NumberPeople which shows the number of people in
the family.
• If MartialStatus is "M", then assume that there are two people in the family, plus the
TotalChildren field.
• If MartialStatus is "S", then assume that there are one person in the family, plus the
TotalChildren field.
 Practice Activity 6-2

5. Create a clustered column chart which shows, for each SalesTerritoryGroup (from the

DimSalesTerritory table) the average of the IncomePerPerson.

6. Having done this, please create a Measure called MeasureIncomePerPerson which gives the

average of the income per person, without using all the Calculated Columns we have just created.

Add this measure into the clustered column chart.

7. Finally, please create a calculated column called RankEQ which calculates the [Link] of the

IncomePerPerson. Please arrange it so that the highest IncomePerPerson is number 1.

Please save the Model developed in this Practice Activity. We will be using it in later Practice
Activities.
Practice Activity 6-2 Solution
 Go to the DimCustomer table, create a Calculated Column called NumberPeople
 In the formula box, use the switch function like this:
NumberPeople = SWITCH(DimCustomer[MaritalStatus],
"M",2,"S",1,0)+DimCustomer[TotalChildren]
 Create a Calculated Column called IncomePerPerson and formula is as shown.

IncomePerPerson = DimCustomer[YearlyIncome] / DimCustomer[NumberPeople]

A clustered column chart which shows, for each SalesTerritoryGroup (from the
DimSalesTerritory table) the average of the IncomePerPerson.
MeasureIncomePerPerson = AVERAGEX(DimCustomer,DimCustomer[YearlyIncome] /
(SWITCH(DimCustomer[MaritalStatus],"M",2,"S",1,0)+DimCustomer[TotalChildren]))
RankEQ =
[Link](DimCustomer[IncomePerPerson],DimCustomer[IncomePerPerson],DESC)
 Columns vs. measures
Calculated column Measure

 Creates a value for each row in a table.  Measures are calculated on demand.
 For this reason, the calculated column can only operate  Power BI calculates the correct value when the user
over columns that exist in the same table. requests it.
 For example, if the table has 1,000 rows, it will have  For example, when you previously dragged the Total
1,000 values in the calculated column. Sales measure onto the report, Power BI calculated the
 Calculated column values are stored in the Power BI correct total and displayed the visual.
.pbix file.  Measures do not add to the overall disk space of the
 Each calculated column will increase the space that is Power BI .pbix file.
used in that file and potentially increase the refresh  Measures are calculated based on the filters that are
time. used by the report user. These filters combine to create
the filter context.
 Knowledge Check

Question 1: Which are calculated on demand?

A. Calculated columns
B. Calculated tables
C. Measures

40
 Knowledge Check

Question 2: Which are calculated based on the filters that are used by the report
user? Calculated columns or measures?
A. Measures
B. Calculated columns

41
 Understanding Context
 How context affects DAX measures is a difficult concept to comprehend. The ensuing visuals will
demonstrate how context affects DAX measures so you can see how they interact together.
 The following three visuals use the exact same DAX measure: Total Sales.

 With Power BI, even though the measure was only defined once, it can be used in these visuals
in different ways.
 Each of the totals is accurate and performs quickly.
 It is the context of how the DAX measure is used that calculates these totals accurately.
 Interactions between visuals will also change how the DAX measure is calculated.
 For instance, if you select the second visual and then select 2015, the results appear as shown in the
following screenshot.

 The definition of the DAX measure has not changed; it's still the original, as shown in the following example:
Total Sales = sum('Sales OrderDetails'[Total Price])
 This scenario is a simple way to explain how context works with DAX.
 Many other factors affect how DAX formulas are evaluated. Slicers, page filters, and more can affect how a DAX
formula is calculated and displayed.
 Relationship Function

Relationship Function Description Example

RELATED(<column>) • It needs a row context FILTER('InternetSales_USD’,

Return value is a single value that is • Can only be used in calculated column expression 'InternetSales_USD'[SalesTerritoryKey]<>1 &&
related to the current row. • It is suitable where the current row context is 'InternetSales_USD'[SalesTerritoryKey]<>2 &&
unambiguous, or as a nested function in an 'InternetSales_USD'[SalesTerritoryKey]<>3 &&
expression that uses a table scanning function such 'InternetSales_USD'[SalesTerritoryKey]<>4 &&
as SUMX. 'InternetSales_USD'[SalesTerritoryKey]<>5)

RELATEDTABLE(<tableName>) • Changes the context in which the data is filtered, SUMX( RELATEDTABLE('InternetSales_USD')
Return value is a table of values. and evaluates the expression in the new context , [SalesAmount_USD])

that you specify.

• Not supported for use in DirectQuery mode
 Example: Relationship Function

• Add column on DimProductCategory table to calculate the StandardCost using the relatedtable function like this:
SUMX(RELATEDTABLE(FactInternetSales),FactInternetSales[ProductStandardCost])

• Check the value if the sum of

ProductStandardCost not
using RELATEDTABLE
function
• Add column on FactInternetSales table to calculate the ProductName using the related function
like this:
RELATED(DimProduct[EnglishProductName])
• In Report view of Matrix visual type, you can drag the fields of ProductName to Rows,
OrderDate to Columns and ProductStandardCost from FactInternetSales table to values as
shown in following figure:
• In Report view of Matrix visual type, you can drag the fields of EnglsihProductName to Rows from
DimProductCategory table, OrderDate to Columns and ProductStandardCost from FactInternetSales
Table to values as shown in following figure:
 Example: Filter function
• Add measure in sum of ProductStandardCost for DimProductCategory table named
ProductStandarCostPromtionKey13.
• You need to calculate the sum of ProductStandardCost if the Promotion key is 13 from FactInternetSales table
Example: Filter function (AllSELECTED )
 The CALCULATE function
• The CALCULATE function in DAX is one of the most important • As shown in the preceding screenshot, Total Sales is
functions that a data analyst can learn. still USD1.35 million, while the 2015 Total Sales is
USD 0.66 million.
• The function name does not adequately describe what it is
intended to do.

• Using the CALCULATE function to create a DAX measure that

will override certain portions of the context that are being used to
express the correct result.

• For instance,

Total Sales for 2015 = CALCULATE(SUM('Sales

OrderDetails'[Total Price]), YEAR('Sales
OrderDetails'[orderdate]) = 2015)
 • When you add the other visual onto the report, as you did previously, and then select 2015, the
results will look like the following image.

• Notice how both measures are now equally the same amount.
• If you were to filter by any other criteria, including region, employee, or product, the filter context
would still be applied to both measures. It's only the year filter that does not apply to that measure.
 Example: CALCULATE with ALL function
PercentOfTotal = SUMX(FactInternetSales,FactInternetSales[ProductStandardCost]) /
CALCULATE(SUMX(FactInternetSales,FactInternetSales[ProductStandardCost]),
ALL(FactInternetSales))
 USERELATIONSHIP function
• Consider the following data model example.

• The solid line between the two tables

(Date and OrderDate columns) indicates that it is the
active relationship,
• A dashed relationship exists between
the Date and ShipDate columns, indicating that it is the
inactive relationship.
• This relationship will never be used unless explicitly
declared in a measure.
 The goal is to build the following report, where you have two visuals: Sales by Ship Date and Sales by
Order Date.

 This function is used to specify a relationship to be used in a specific calculation and is done without overriding any existing relationships.
 It is a beneficial feature in that it allows developers to make additional calculations on inactive relationships by overriding the default active
relationship between two tables in a DAX expression, as shown in the following example:

Sales by Ship Date = CALCULATE(Sales[TotalPrice],

USERELATIONSHIP('Calendar'[Date], Sales[ShipDate]))
 Knowledge Check

Question 1: Which DAX function evaluates an expression in a modified filter

context?
A. SUMX
B. CALCULATE
C. ALL

56
 Knowledge Check

Question 2: Why would you want to override the default context?

A. To create measure that behave according to the user’s selection
B. To create measure that behave according to your intentions, regardless of what the
user selects

57
 Semi-additive Measures
SalesAmountFormula =
CALCULATE(SUM(FactInternetSales[SalesAmount]),LASTDATE(FactInternetSales[DueDate]))

you would need to tell Power BI

not to add the measure but instead
take the last day for each month of
sales and assign it to any visual.
 Time-Intelligence
• All data analysts will have to deal with time. Dates are important, so we highly recommend that you create or import a
dates table. This approach will help make date and time calculations much simpler in DAX.

• While some time calculations are simple to do in DAX, others are more difficult. For instance, the following screenshot
shows what happens if you want to display a running total.

• Notice that the totals increment for each month but then reset
when the year changes.
• DAX makes this process fairly simple, as shown in the
following example:
YTD Total Sales = TOTALYTD ( SUM('Sales
OrderDetails'[Total Price]) , Dates[Date] )
 Time-Intelligence
• DAX Syntax
TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])
 Evaluates the year-to-date value of the expression in the current context.
 Parameter

 Return value
A scalar value that represents the expression evaluated for the current year-to-date dates.
• You can use other DAX syntax:
TOTALMTD(<expression>,<dates>[,<filter>]) (Evaluates the value of the expression for the month to date)
TOTALQTD(<expression>,<dates>[,<filter>]) (Evaluates the value of the expression for the dates in the quarter to date)
 Total Sales Previous Month = CALCULATE ( sum('Sales OrderDetails'[Total Price]) , PREVIOUSMONTH(Dates[Date]) )

 PREVIOUSMONTH function returns a table that contains a column of all dates from the previous month,
based on the first date in the Dates column in the current context.
 Example: Using DATESMTD and TOTALMTD
CALCULATE(SUM(FactInternetSales[SalesAmount]), TOTALMTD(SUM(FactInternetSales[SalesAmount]),
DATESMTD(FactInternetSales[DueDate])) FactInternetSales[DueDate])
Example: Using PARALLELPERIOD
CALCULATE(SUM(FactInternetSales[SalesAmount]),PARALLELPERIOD
(FactInternetSales[DueDate],2,MONTH))
 Practice Activity 6.4

Let's practice the Time Intelligence functions. We will be using the model that we have used in the previous
Practice Activities. All of the fields in this Practice Activity are from the FactInternetSales table.
1. Create a Matrix visualization, with DueDate in the Rows, and the Earliest DueDate and Latest Due Date in
the Value.
2. Add a Measure called Duration to the Matrix, which is the LASTDATE minus the FIRSTDATE of the Due
Date, plus one. To convert it to a number, use the FORMAT function and the format "0". Check that it works at
different date hierarchy levels.
3. Add a Measure to the Matrix which shows the STARTOFYEAR.
4. Remove the Duration, Earliest and Latest Due Date fields, and change the DueDate in the Rows from a "Date
Hierarchy" to the DueDateField.
5. Add the sum of SalesAmount to the Matrix, and create a Measure with a Year to Date figure.
6. Calculate a rolling sum over the current and the previous days. Use the CALCULATE and the
DATESINPERIOD function, using the DueDate column, FIRSTDATE([DueDate]), -2, DAY
7. Use the SAMEPERIODLASTYEAR function to get the SalesAmount from the previous year.
8. Alter this formula to use the PARALLELPERIOD function to get the total for the current month.
Practice Activity 6-4 Solution

 Create a Matrix which contains:

Rows: "DueDate" from FactInternetSales
Value: Earliest DueDate and Latest Due Date
 Create a measure in FactInternetSales Table named “Duration”

Duration = format(LASTDATE(FactInternetSales[DueDate])-
FIRSTDATE(FactInternetSales[DueDate])+1,"0")

 Add Duration to the value of Matrix

 StartOfYearMeasure=STARTOFYEAR(FactInternetSales[DueDate])

Modify the Matrix visual by removing the Duration, Earliest and Latest Due Date fields, and change the DueDate in
the Rows from a "DateHierarchy" to the DueDateField.
SalesAmountYTD =
CALCULATE(sum(FactInternetSales[SalesAmount]),DATESYTD(FactInternetSales[DueDate]))
RollingSum =
CALCULATE(sum(FactInternetSales[SalesAmount]),DATESINPERIOD(FactInternetSales[DueDate],
FIRSTDATE(FactInternetSales[DueDate]),-2,DAY))
SalesAmountPreviousPeriod =
CALCULATE(sum(FactInternetSales[SalesAmount]),SAMEPERIODLASTYEAR(FactInternetSales[DueDate]))
SalesAmountPreviousPeriod =
CALCULATE(sum(FactInternetSales[SalesAmount]),PARALLELPERIOD(FactInternetSales[DueDate],0,MONTH))
 %OfCurrentMonth =
sum(FactInternetSales[SalesAmount]) /FactInternetSales[SalesAmountPreviousPeriod]

 Change the format of %OfCurrentMonth column with Percentage format.

 Knowledge Check

Question 1: What type of Measure uses SUM to aggregate over one set of dimensions and a different
aggregation over a different set of dimension?

A. Additive
B. Aggregate
C. Semi-additive

72
 Knowledge Check

Question 2: What type of functions enable you to manipulate data using time periods?
A. Time intelligence
B. Compare functions
C. Value functions

73
 Knowledge Check

Question 3: Which two functions will help you compare dates to the previous month?
A. TOTALYTD and PREVIOUSMONTH

B. CALCULATE and TOTALYTD

C. CALCULATE and PREVIOUSMONTH

74
 Summary

 Calculated column is a column that you add to an

existing table (in the model designer) and then create a
DAX formula that defines the column's values.
 DAX is a formula expression language  values are calculated for each row as soon as the
used in Analysis Services, Power BI, and
formula is entered and stored in the in-memory data
Power Pivot in Excel.
model.
 DAX formulas include functions, operators,
and values to perform advanced
calculations and queries on data in related
tables and columns in tabular data
models. Measures are dynamic calculation formulas where the
results change depending on context.
They are used in reporting that support combining and
filtering model data by using multiple attributes
 Measures are created by using the DAX formula bar in
the model designer.
 Summary

• DAX Functions
 Relationship Functions

• Managing and utilizing relationships between tables such as RELATED,

RELATEDTABLE, USERELATIONSHIP, CROSSFILTER.

 Filter Functions

• Some of the most complex and powerful, and differ greatly from Excel functions such as ALL, ALLEXCEPT,
ALLSELECTED, CALCULATE and so on.
• The filtering functions let you manipulate data context to create dynamic calculations.

 Time Intelligence Functions (DATEADD, DATESBETWEEN, DATESINPERIOD,DATESMTD, DATESQTD, DATESYTD, LASTDATE,

SAMEPERIODLASTYEAR, TOTALYTD, TOTALMTD, TOTALQTD)

• To manipulate data using time periods, including days, months, quarters, and years, and then build and
compare calculations over those periods.
• Before using any time-intelligence functions, make sure to mark one of the tables containing date column
as Date Table.