This section shows how to create several useful custom date functions using calculate, a calendar table and time intelligence functions.
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.
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:
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) )
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) )
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 OutWith all examples above, you can use any aggregation in place of SUM(TransactionData[revenue]).
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
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.
To automate year to date (YTD) sales, define the following measure in Power BI:
YTD_This_Year = TOTALYTD(SUM(TransactionData[Revenue]),CalenderDate[Date])
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.
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 OutName | 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