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?

1. Create a date table – This will not be part of this article. Please refer to the article before proceed with Time Intelligence functions
2. Create a generic measure. Example: Sales Actuals = SUM(Sales[SalesAmount])
3. Add a time intelligence function to the generic measure

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:

Best Practices in Power BI

YoY % = DIVIDE([YoY Variance], [Sales LY])

Naming Conventions

Preview Full Image
Tags:
Power BI

Leave a Comment