Well first you might be wondering why would I need the Quarter totals or Quarter to date (QTD) at the month level. There are several reasons you may need this.
- To calculate the percent of the month to the quarter totals.
- Give a running total of the current quarter displayed in each month
- Financial dashboard where you want to allow for the Month to be a slicer but show the Month Totals, Quarter totals Year totals etc…
When I got asked this question at first I thought this must be easy, just use the TOTALQTD function and a little tinkering with the date table to put the quarter end date and it should work. Well that’s part of the solution and let me take you through that so I can also show why that doesn’t work completely. For this example I am going to use the AdventureWorksDW2012 database. So first step is in PowerPivot lets bring in the DimDate and the FactInternetSales Table.
Once we have these two tables we can setup this simple example. First step is to add the quarter end date to the dim date table which can be done by going to the Dim Date in the Data View and going all the way to the end of the table columns and you should see Add Column and click an empty cell and enter the following formula: =ENDOFQUARTER(DimDate[FullDateAlternateKey]). You can then right click on the column heading and click rename column and now name it End of Qtr. It should look like this.
Now that we have the End of Qtr date it gives is a reference point to be able to summarize on for any measure so lets go to the FactInternetSalesTable and add a formula to calculate a Total Quarter for the freight. In the area below the table add a formula that looks like the following: QTD Freight:=TOTALQTD(sum(FactInternetSales[Freight]),DimDate[End of Qtr]). If you look at the formula we are saying do a TOTALQTD for the Freight amount and use the End of Qtr field from Dim Date. Now lets see why this doesn’t completely work. Add a pivot table and put FiscalYear, FiscalQuarter, EnglishMonthName in row labels and then QTD Freight and Freight in the Values and it should look like this.
You will notice that it is giving us at the year level the Total of the last Quarter, at the quarter level the total of the quarter but at the month level it is just totaling the Month. Now at first this threw me off as I told it to use the quarter day and the quarter day for those months are all the same but it is not totaling the quarters up. Then I realized why, and it’s because you have to look at each row as a filter. By default the row for April is filtered on April data for quarter 4 of 2006. Luckily this can be easily fixed. Go back to the Power Pivot and lets edit the formula for the QTD Freight to add one more thing to make the formula look like this: QTD Freight:=TOTALQTD(sum(FactInternetSales[Freight]),DimDate[End of Qtr],all(DimDate)) Notice the addition of the all(DimDate). The reason for this is we now need to tell the calculation that even though the row is filtered on the month for this calculation use the whole DimDate table. Now it should look like this.
Now you see the QTD Freight calculating correctly and show at the month level the total for the entire quarter.
Another SQL Geek 