POWER BI: Modeling

Date Functions

This section shows how to create several useful custom date functions using calculate, a calendar table and time intelligence functions.

Moving Annual Total

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.

EXAMPLE:

Create a measure in the power BI formula bar called MAT which returns the moving annual total for any context.

                   MAT (moving annual total) = CALCULATE(     
                            SUM(TransactionData[Revenue]),
	                        DATESINPERIOD(CalenderDate[Date],LASTDATE(CalenderDate[Date]),-1,YEAR) 
	                        )      
	            

This formula uses CALCULATE where the first parameter is the value that gets returned. The second parameter specifies the date range using the DATESINPERIOD function. DATESINPERIOD has four arguments:

  • The date column used
  • The last date that you want to return
  • The offset
  • What time period you want to return

Try It Out
Moving Monthly Total

Similar to a MAT a Moving Monthly Total (MMT) is more granular in time. It returns dates up to one month prior. (ie. on February 15th a moving monthly total return all dates from Jan 15th to Feb 15th

                   MAT (moving annual total) = CALCULATE(
                            SUM(TransactionData[Revenue]),
	                        DATESINPERIOD(CalenderDate[Date],LASTDATE(CalenderDate[Date]),-1,MONTH) 
	                        )      
	            

Try It Out
Moving Quarterly Total

Similar to a MAT. A Moving Quarterly Total returns dates up to one quarter prior.

                   MAT (moving annual total) = CALCULATE(
                            SUM(TransactionData[Revenue]),
	                        DATESINPERIOD(CalenderDate[Date],LASTDATE(CalenderDate[Date]),-1,QUARTER) 
	                        )      
	            

Try It Out
Moving 6 Month Total

To get the current last 6 months of data.

                   MAT (moving annual total) = CALCULATE(
                            SUM(TransactionData[Revenue]),
	                        DATESINPERIOD(CalenderDate[Date],LASTDATE(CalenderDate[Date]),-2,QUARTER) 
	                        )      
	            

or

                   MAT (moving annual total) = CALCULATE(
                            SUM(TransactionData[Revenue]),
	                        DATESINPERIOD(CalenderDate[Date],LASTDATE(CalenderDate[Date]),-6,MONTH) 
	                        )      
	            

Since a quarter is 3 months, we offset by -2 to get the previous two quarters (6 months). As usual we put in the last date of the calendar date column to get the last date of the previous two quarters. Alternatively instead of QUARTER we can use MONTHS and offset by -6.

In all the examples above, the moving total finds the last date where there is revenue in the transaction data table and returns all values specified by the offset and time period used.

Try It Out

With all examples above, you can use any aggregation in place of SUM(TransactionData[revenue]).

Running Total

Returns all dates from a given time period up to the most current date where there is data.

This example finds all revenue from the beginning of time up until the last date.

                      Running Total = CALCULATE(
                              SUM(TransactionData[Revenue]),
                              FILTER(ALL(CalenderDate[Date]),CalenderDate[Date] <= MAX(CalenderDate[Date])))
                              
                
Try It Out
Year to Date (YTD) Comparison

The task is to get year to date sales for this year and year to date sales at the same time last year. Finally, compare the percentage difference between the two.

Step 1: YTD Sales:

To automate year to date (YTD) sales, define the following measure in Power BI:

                      
                    YTD_This_Year = TOTALYTD(SUM(TransactionData[Revenue]),CalenderDate[Date])
                      
                
  • TOTALYTD takes a measure and a date column as parameters.
Step 2: YTD Sales from Last Year:

To get YTD sales for the same period but last year:

                      
                   YTD_Last_Year = TOTALYTD(SUM(TransactionData[Revenue]),SAMEPERIODLASTYEAR(CalenderDate[Date]))
                      
                

Notice this formula is almost identical to YTD This Year. Simply replace CalendarDate[Date] with SAMEPERIODLASTYEAR(CalendarDate[Date]). The SAMEPERIODLASTYEAR function works to offset all the dates by one year.

Step 3: Compare the percentage difference:

To find the percentage difference from last year use the percentage difference formula:

$ \dfrac{(finalValue - initialValue)}{intitalValue} $

In the formula bar write:

                      
                   %_Difference_from_Last_Year = DIVIDE([YTD This Year] - [YTD Last Year],[YTD Last Year],0)
                      
                

The divide function handles zero division error. The baseline metric (initial value) is YTD sales from last year.

Try It Out
Summary of functions used
Name Description
ALL Returns all rows in a table or column. Ignores other filters applied (i.e from slicers)
DATESINPERIOD Returns a table of dates. Create as a new table, or use as a filter in a CALCULATE expression. Intervals can be specifed as day, month, quarter or year.
DATESBETWEEN Returns a table of dates
FILTER Returns a filtered table. A subset of the original data table used.
FIRSTDATE Return the same period from last year
LASTDATE Returns the last date in column
TOTALYTD Returns year to date values for an expresesion

See also: Resources for Time Intelligence Functions
Up Next: Stats Functions


Dash-Intel is a Power BI and Tableau resource site for data visualization and building BI dashboards.

Data Analyst & Consultant

Copyright 2015-2023 Dash-Intel.com Terms