Sometimes in a report, you need a running total of an amount, not just the daily totals. In the previous pivot tables, I have shown reports like the one in the following figure which displays data by date. (This week I return to using the Contoso 2012 data if you want to follow along.) Note that I have chosen to show the subtotals for the month at the bottom of the group only and I use a sort by column to sort the months in chronological order rather than alphabetical order. I have covered these techniques in previous blog entries.
This report accurately portrays the sales by each day of the month. However, suppose I want to see the cumulative sales within the month. In other words, on January 1, 2007 I want to see 6,085,839.18, but on January 2, 2007, I want to see 12,356,496.35 which is the sum of sales for January 1 and January 2.
There is a way to do this using the DAX time function, TOTALMTD that stands for Total Month-To-Date. The basic syntax for this function is:
I can use this function to calculate a new measure in the FactSales table since that table contains the SalesAmount of each sale. The expression I will use is:
This DAX function sums the Sales Amount column using the dates provided in the second parameter. For this example, the date column I point to is DimDate[DateKey] which is related to the DateKey column in the FactSales table. Why not just use the DateKey column in FactSales? The column that is used to provide dates must have unique dates in it and DateKey in FactSales is not unique. However, it is unique in DimDate.
Another interesting point is that the date column selected for the TOTALMTD function will not work properly if the row definition is based on DateKey from FactSales and TOTALMTD uses the date column DimDates[DateKey].
In any case, my final measure expression is:
After formatting my data as currency, I now have a column with the daily sales and a column that shows the sales to date for the current month.
There are similar functions for calculate totals by quarter and by year. They are TOTALQTD and TOTALYTD respectively. The only difference between these functions is on which date they choose to reset the total back to zero.
Another interesting function is the PARALLELPERIOD function that I can use to retrieve a set of dates from a different time interval from the current date. If all I want is to compare sales for the current month to the previous month, I could use the PREVIOUSMONTH function in an expression such as:
But what if I wanted to get the sales from three months previous. On method would be to nest PREVIOUSMONTH functions like this:
However, that expression is not only hard to read, it is far too easy to make a mistake matching parenthesis correctly. A simpler function too use is the PARALLELPERIOD function. This function allows me to define a previous period such as three months ago using the much simpler expression:
With this function, the first parameter is still the date column from my dimension table. The second parameter is the number of intervals I want to offset my calculation. In this case, because I want to calculate the totals for three months ago, I use a negative value, -3. The third parameter defines the type of period I want to use and includes values ‘month’, ‘quarter’, ‘year’. Unfortunately you cannot use this function to calculate the sales from three days ago. This is a topic I’ll take up next time.
That’s it for this time. Next time I’ll take a look at some ways to calculate running averages.