Time Intelligence in Power BI
What is Time Intelligence?
Time Intelligence functions in DAX are a set of functions that give you insight from the date and time dimensions. Most of the analysis by date and time are in that category, as an example: year to date, quarter to date, month to date, same period last year calculations etc.
Power BI makes it simple to work with time intelligence. Once you understand the concepts, you will be able to do a wide range of complex time-based reporting.
To make time intelligence work in Power BI, you must need a calendar/date table. It will not work without it. I wrote an article about creating Calendar (Date) table in Power BI.
In this article, I’ll go through a few very useful time intelligence functions available in Power BI, as well as the steps for using them.
What are the steps to use these time intelligence functions?
Timeline
AGGREGATIONS
Year to Date (YTD)
Year to date (YTD) refers to the period of time beginning the first day of the current calendar year or fiscal year up to the current date or slicer selected date
YTD = CALCULATE([Sales Actuals], DATESYTD('Date'[Date])) -- Option 1
YTD = TOTALYTD([Sales Actuals], 'Date'[Date]) -- Option 2 (sugar syntax)
Built-in time intelligence calculations (TOTALYTD, DATESYTD etc.) DO NOT work if you are using a direct query connection to your data source rather than importing data.
In that case, we can use customized calculation and it will work fine as well.
In that case you can customize the measure using simple operations.
An example of YTD (Custom) is like:
YTD (custom) =
CALCULATE([Sales Actuals],
FILTER(
ALL('Date’),
'Date'[Year] = MAX('Date'[Year])
&&
'Date'[Date] <= MAX('Date'[Date])))
In this case we are using Year column from our Calendar table.
The same result can be achieved using date column converted into year:
YTD (custom) v2 =
CALCULATE (
[Sales Actuals],
FILTER (
ALL ('Date'[Date]),
'Date'[Date] <= MAX ('Date'[Date])
&& YEAR ('Date'[Date]) = YEAR ( MAX ('Date'[Date]))))
Quarter to Date (QTD)
Quarter-to-date (QTD) is a time interval that captures a company’s financial information from the start of a quarter up to the current date or slicer selected date
QTD =
CALCULATE(
[Sales Actuals],
DATESQTD('Date'[Date])) -- Option 1
QTD = TOTALQTD([Sales Actuals],'Date'[Date]) -- Option 2 (sugar syntax)
Built-in time intelligence calculations (TOTALQTD, DATESQTD etc.) DO NOT work if you are using a direct query connection to your data source rather than importing data.
In that case, we can use customized calculation and it will work fine as well.
Refer to the version v2 at the same way as YTD in case you don’t have Year
QTD (custom) =
CALCULATE (
[Sales Actuals],
FILTER (
ALL ( 'Date’),
'Date'[Year] = MAX ('Date'[Year])
&&
'Date'[Quarter] = MAX ('Date'[Quarter])
&&
'Date'[Date] <= MAX ('Date'[Date])))
Month to Date (MTD)
Month to Date (MTD) is the period starting from the beginning of the current month (selected month) up until now or selected slicer date, but not including today’s date, because it might not be complete yet
MTD =
CALCULATE(
[Sales Actuals],
DATESMTD('Date'[Date])) -- Option 1
MTD = TOTALMTD([Sales Actuals],'Date'[Date]) -- Option 2 (sugar syntax)
Built-in time intelligence calculations (TOTALMTD, DATESMTD etc.) DO NOT work if you are using a direct query connection to your data source rather than importing data.
In that case, we can use customized calculation and it will work fine as well.
MTD (custom) =
CALCULATE(
[Sales Actuals],
FILTER(
ALL('Date'),
'Date'[Year] = MAX ('Date'[Year])
&&
'Date'[Month] = MAX ('Date'[Month])
&&
'Date'[Date] <= MAX ('Date'[Date])))
Fiscal or Financial Year to Date
Calculating the calendar year to date was easy, how about the fiscal or financial calculation?
Do we have a function for those? No.
However, there is a parameter that you can add in the expression and that makes the calculation fiscal.
The <year end date> parameter is an optional parameter which we have not used in the previous example.
When you do not assign a value for this parameter, the default value would be considered which is 31st of Dec of each year. If you want to specify a value for this parameter, this is an example of how you can do that.
For example, Company’s fiscal year starts on June 1. It is now June 15.
The YTD with reference to the calendar and fiscal year up until June 15 is as follows:
Company’s Calendar YTD: Period from January 1 to June 15.
Company’s Fiscal YTD: Period from June 1 to June 15.
Please refer also to the Timeline diagram in the beginning of the article
FYTD = CALCULATE([Sales Actuals], DATESYTD('Date'[Date], "05/31")) -- Option 1
FYTD =
TOTALYTD([Sales Actuals],
'Date'[Date],
ALL('Date'[Date]),"05/31") -- Option 2
Simple visual comparison between YTD and FYTD
Running Total (Cumulative Sum)
A running total, or cumulative sum, is a sequence of partial sums of any given data set. A running total is used to display a summary of data as it grows over time
Sales Running Total =
IF(
[Sales Actuals] <> BLANK(),
CALCULATE(
[Sales Actuals],
FILTER(
ALL('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date]))))
The same result can be achieved using the quick measures inside Power BI
Sales RT Quick Measure =
CALCULATE(
[Sales Actuals],
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
)
)
A running total can also be calculated not only with dates, but for sequence of unique values (IDs)
This approach is very useful if you want to create Profit/Loss Income Statement.
Stay tuned for an article of P&L Income Statement in Matrix visual with custom subtotals like Gross Profit, Total COGS, EBITDA etc.
Sales RT by Category =
-- In this case we calculate running total by products
VAR CategoryMax = MAX ( dimProduct[ProductKey] ) --or SELECTEDVALUE(dimProduct[ProductKey])
RETURN
CALCULATE (
[Sales Actuals],
FILTER(
ALL ( dimProduct),
dimProduct[ProductKey] <= CategoryMax
))
Moving Annual Total (MAT)
A Moving Annual Total (MAT) finds the total value over the last 12 months. This can be computed day over day, month over month or quarter over quarter depending on how you are grouping your data. If you are reporting sales figures by month then the MAT will change month over month to reflect the last 12 months of sales. If you have seasonality in your data where sales are particularly high in some months and low in others, then a MAT will eliminate these seasonal effects. You can then plot this figure for forecasting or predicting trends.
Last N Months =
VAR Months = 12 -- change this number by your needs
RETURN
CALCULATE (
[Sales Actuals],
DATESINPERIOD (
'Date'[Date], -- returns period from date column
MAX ('Date'[Date]), -- starting from MAX date
-Months, -- shifting it back n intervals
MONTH -- each interval being a month
))
The same result can be achieved using years shift as well, instead of months shift
Last N Months v2 =
VAR Years = 1 -- change this number by your needs
RETURN
CALCULATE (
[Sales Actuals],
DATESINPERIOD (
'Date'[Date], -- returns period from date column
MAX ('Date'[Date]), -- starting from MAX date
- Years, -- shifting it back n intervals
YEAR -- each interval being a year
))
We can calculate MAT at a quarterly base as well.
Moving Quarterly Total returns dates up to one quarter prior
Last N Quarters =
VAR Quarters = 1 -- change this number by your needs
RETURN
CALCULATE (
[Sales Actuals],
DATESINPERIOD (
'Date'[Date], -- returns period from date column
MAX ('Date'[Date]), -- starting from MAX date
- Quarters, -- shifting it back n intervals
QUARTER -- each interval being a quarter
))
SHIFT
Sales Last Year (LY) / Last Quarter (LQ) / Last Month (LM)
Sales LY = CALCULATE(
[Sales Actuals],
DATEADD('Date'[Date], -1,YEAR)) --Option 1
Sales LY = CALCULATE([Sales Actuals], SAMEPERIODLASTYEAR('Date'[Date])) -- Option 2
Built-in time intelligence calculations like DATEADD, DO NOT work if you are using a direct query connection to your data source rather than importing data.
In that case, we can use customized calculation and it will work fine as well.
Sales LY (custom) =
CALCULATE(
[Sales Actuals],
FILTER (
ALL ('Date'),
'Date'[Year] = MAX ('Date'[Year])-1))
We can calculate Last Quarter (LQ) and Last Month (LM) at a quarterly and monthly base as well.
Sales LQ = CALCULATE(
[Sales Actuals],
DATEADD('Date'[Date], -1,QUARTER))
Sales LQ = CALCULATE([Sales Actuals],
PREVIOUSQUARTER('Date'[Date])) -- Option 2
Sales LM = CALCULATE(
[Sales Actuals],
DATEADD('Date'[Date], -1,MONTH))
Sales LM = CALCULATE([Sales Actuals],
PREVIOUSMONTH('Date'[Date])) -- Option 2
In the same way we can calculate YTD/QTD/MTD for the Last Year (LY) / Previous Year (PY)
LYTD/LQTD/LMTD or PYTD/PQTD/PMTD
Sales LYTD = CALCULATE(
[YTD],
DATEADD('Date'[Date], -1,YEAR)) --Option 1
Sales LYTD = CALCULATE([YTD], SAMEPERIODLASTYEAR('Date'[Date])) --Option 2
Sales LYTD = TOTALYTD([Sales Actuals],
SAMEPERIODLASTYEAR('Date'[Date])) --Option 3
Sales LQTD = CALCULATE(
[QTD],
DATEADD('Date'[Date], -1,QUARTER)) --Option 1
Sales LQTD = CALCULATE([QTD], SAMEPERIODLASTYEAR('Date'[Date])) --Option 2
Sales LMTD = CALCULATE(
[MTD],
DATEADD('Date'[Date], -1,MONTH)) --Option 1
Sales LMTD = CALCULATE([MTD], SAMEPERIODLASTYEAR('Date'[Date])) --Option 2
COMPARISON
Time Comparison – Current Year vs. Last Year
Year-over-Year (YOY) is a calculation commonly used in economics or financial data to show how information from one time period compares to the year prior
YoY Variance = [Sales Actuals] - [Sales LY]
Most common in data we have a situation when actuals or last year is missing or is 0 or blanks. In that case we can ignore sales without data. For example if actual value is present, but last year the same period is not available, the calculation will be like 1234 – 0 = 1234 or 1234*100 = 123400 % increase which doesn’t make any sense. What we want is to compare same periods with data.
In such cases we can modify the measure to ignore the missing data for one or both components of the formula (CY or LY)
YoY Variance (ignore blanks) =
IF(
NOT ISBLANK ([Sales Actuals])
&&
NOT ISBLANK ([Sales LY]),
[Sales Actuals] - [Sales LY])
YoY Variance (ignore 0) =
IF (
[Sales Actuals] <> 0
&& [Sales LY] <> 0,
[Sales Actuals] - [Sales LY],
BLANK()
)
Compare the percentage difference (increase/decrease)
To find the percentage difference from last year use the percentage difference formula: (finalValue − initialValue) / intitalValue
The DIVIDE function handles zero division error
You can find more about using DIVIDE function in this article:
YoY % = DIVIDE([YoY Variance], [Sales LY])
Naming Conventions
Tags:
Power BIRecent Comments
Archives
Categories
Search
By transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyBy transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyBy transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyCategories
- Power BI (7)