Use DAX time intelligence functions in Power BI Desktop models Flashcards
What is time intelligence in Power BI?
Time intelligence involves calculations over dates, months, quarters, or years to analyze data over time.
How does DAX handle time intelligence?
DAX modifies the filter context for date filters to calculate metrics like year-to-date (YTD) revenue.
Give an example of a time intelligence calculation.
Calculating the year-to-date revenue by adjusting the filter context to include all dates from the start of the year to the current date.
What are some questions that time intelligence calculations can help answer?
What is the accumulation of revenue for the year, quarter, or month?
What revenue was produced for the same period last year?
What is the revenue growth over the same period last year?
What is the purpose of DAX time intelligence functions in Power BI?
To simplify the task of modifying date filter context for calculations over standard date periods like years, quarters, and months.
What is a requirement for using time intelligence DAX functions?
Having a date table in your model that contains unique, non-BLANK dates and spans full years.
What does the TOTALYTD function do in DAX?
Evaluates an expression for year-to-date (YTD) in the current filter context.
How do you calculate year-to-date revenue using DAX?
Revenue YTD = TOTALYTD([Revenue], ‘Date’[Date], “6-30”)
What does the SAMEPERIODLASTYEAR function do in DAX?
Returns a table of dates shifted one year back in time from the current filter context.
How do you create a measure to calculate revenue for the prior year in DAX?
Revenue PY = CALCULATE([Revenue], SAMEPERIODLASTYEAR(‘Date’[Date]))
What do the FIRSTDATE and LASTDATE DAX functions return?
The first and last date in the current filter context for the specified column of dates.
How do you calculate the number of new customers in a time period?
By creating a measure that subtracts the count of distinct customers before the period from the count life-to-date (LTD).
What is a common use of snapshot calculations in DAX?
To summarize values like inventory stock levels, ensuring you only sum across dimensions other than date.
How can you calculate stock on hand for a single date using DAX?
Using the LASTDATE function to filter the SUM of UnitsBalance.
What function can be used to handle missing dates in snapshot calculations?
The LASTNONBLANK function, which finds the last date with a non-blank value.
