Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Microsoft Data Modeling in Power BI > DAX - Time Intelligence > Flashcards

DAX - Time Intelligence Flashcards

1
Q

What do time intelligence functions do?

A

Enable you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.

They are particularly useful for trend analysis, forecasting, and performance comparison.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are the two types of time intelligence functions?

A
  1. Functions that returns a scalar value without requiring CALCULATE
  2. Functions that returns a table, which has to be used as a filter in a CALCULATE statement
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is required for time intelligence functions to work correctly?

A

A date dimension table with a column of dates.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are time-related dimensions?

A
  • Date
  • Weeks
  • Months
  • Quarters
  • Years
  • Annual Periods
  • YTD
  • Etc
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Benefits of time intelligence

A
  • Identifying trends
  • Forecasting and predictive analysis
  • Real-time performance monitoring
  • Compariative analysis (example year over year)
  • Sales and marketing optimization
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What are the three classifications of time intelligence?

A
  • Time Comparison Functions
  • Aggregate Functions
  • Information Functions
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What does a Time Comparison Function do?

A

Compares one date or time to another.

For instance, comparing total revenue with the revenue from the last quarter.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What do aggregate time intelligence functions do?

A

Show the year-to-date, month-to-date or anything similar.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What do information functions do?

A

Provide snapshots of information

Like a month-opening or year-end balance. These functions are especially important in financial management.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What does summarizing data over time mean?

A

Identifying trends, patterns, and anomalies in performance over a specific period.

Examples
Sales per Quarter
Annual Growth

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

TOTALYTD syntax

A

TOTALYTD(expression, DateColumn, filter, YearEndDate)

Filter and YearEndDate are optional arguments

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

TOTALYTD example

A

Sales_YTD=
TOTALYTD(
SUM(Sales[Total Sales]), //Sum total sales
Sales[OrderDate].[Date] //Field to use for the date
)

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How does TOTALYTD work?

A

It returns the desired expression, for example SUM(Sales), from January 1 to the specified date of the current year.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

DATESYTD syntax

A

DATESYTD(DateColumn, YearEndDate)

YearEndDate is an optional argument. The DateColumn is the column you want to use to calculate YTD

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How does DATESYTD work?

A
  • It returns a single column table that has dates for the year to date in the current filter context.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is a good scenario in which to use TOTALYTD?

A

When you need running totals

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are two other functions that work similarly to DATESYTD?

A
  • DATESMTD
  • DATESQTD
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Where are DATESYTD, DATESMTD and DATESQTD used?

A

In the CALCULATE function as a filter context.

19
Q

DATESYTD Example

A

Sales_YTD=
CALCULATE(
SUM(Sales[Total Sales]), //Sum of total sales
DATESYTD(Sales[OrderDate].[Date] //Filter context
)

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

20
Q

What does DATESBETWEEN return?

A

All dates in a specified period

21
Q

DATESBETWEEN syntax

A

DATESBETWEEN(DateColumn, StartDate, EndDate)

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

22
Q

DATESBETWEEN example

A

Summer Sales=
CALCULATE(
SUM(Sales[Total Sales]),
DATESBETWEEN(Sales[OrderDate], //Date Column
DATE(2023, 6, 1), //StartDate
DATE(2023, 8, 31) //EndDate
))

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

23
Q

What do Time Comparison functions do?

A

Compare sets of data over specific periods.

Example
Comparing sales from this month to last month

24
Q

What are three Time Comparison functions?

A
  • SAMEPERIODLSATYEAR
  • DATEADD
  • PARALLELPERIOD
25
Q

SAMEPERIODLASTYEAR

A
  • Returns a table that contains a column of dates shifted one year back in time from the dates in the specified DateColumn
  • In other words, it compares the current period against the same period last year
26
Q

SAMEPERIODLASTYEAR syntax

A

SAMEPERIODLASTYEAR(DateColumn)

27
Q

SAMEPERIODLASTYEAR Example

A

Revenue PY=
CALCULATE(
[Revenue],
SAMEPERIODLASTYEAR(Sales[OrderDate].[Date])
)

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

28
Q

Example of use for SAMEPERIODLASTYEAR

A

Highlight
- Seasonal Trends
- Marketing Campaign Effectiveness
- Overall Business Health

Often used in retail and e-commerce for Year over Year comparisons.

29
Q

Revenue YoY% Example

A

Revenue Yoy%=
VAR RevenuePreviousYear=
CALCULATE(
[Revenue], //Measure
SAMEPERIODLASTYEAR(Sales[OrderDate].[Date])
)

RETURN
DIVIDE([Revenue]-RevenuePreviousYear, RevenuePreviousYear)

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

30
Q

PARALLELPERIOD syntax

A

PARALLELPERIOD(DateColumn, NumberIntervals, UnitOfTime)

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

31
Q

Example of using PARALLELPERIOD

A

Compare this year’s sales with those from two years ago.

32
Q

How does DATEADD work?

A

Returns a column of dates either added forward or backward in time by the specified number of intervals in the current context.

33
Q

DATEADD Syntax

A

DATEADD(DateColumn,
NumberOfIntervals, Interval)

For example, the interval could be the year, quarter or month.

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

34
Q

What does putting - in front of DATEADD do?

A

Subtracts the interval. Otherwise the default is to add the interval.

35
Q

DATEADD Example

A

Sales Comparison=
CALCULATE(
[Revenue], //Measure
DATEADD(Sales[OrderDate].[Date], -1, MONTH)
)

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

36
Q

PREVIOUSYEAR syntax

A

PREVIOUSYEAR(DateColumn, Optional YearEndDate)

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

37
Q

Where is the PREVIOUSYEAR function instrumental?

A

When making Y-o-Y (Year Over Year) comparisons.

38
Q

What are two related functions to PREVIOUSYEAR?

A
  • PREVIOUSMONTH
  • PREVIOUSQUARTER
  • PREVIOUSDAY
39
Q

What do the PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER and PREVIOUSYEAR functions do?

A

They return:
1. All dates in the previous year
2. Previous Day
3. Previous Month
4. Previous Quarter

40
Q

What are PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER and PREVIOUSYEAR used for?

A

Historical evaluation of data

41
Q

NEXTYEAR syntax

A

NEXTYEAR(DateColumn, YearEndDate)

You MUST have a date dimension table with a column of dates for time intelligence functions to work correctly.

42
Q

What are three other functions similar to NEXTYEAR?

A
  1. NEXTDAY
  2. NEXTMONTH
  3. NEXTQUARTER
43
Q

Where are the NEXT functions used?

A
  1. Projections
  2. Forecasts
44
Q

Example using NEXTMONTH

A

Use NEXTMONTH to project if you’re on track to meet next months goals based on the current month’s data.

Microsoft Data Modeling in Power BI (14 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions