TOTALQTD() Not Showing Running Total for Calendar Quarters in Matrix Row

[RokuCap]

Chapter 15 of 'Supercharge Power BI' by Matt Allington talks about Time Intelligence Functions like TOTALMTD() and TOTALQTD().

It recommends to create a matrix to test the measures using the contiguous date range from the 'Calendar' Table in Calendar[Date] and slicers to compare the Year/Month/Quarter-to-date value with the matrix summed total.

I'm having issues with this measure: Total Sales Quarter to Date = TOTALQTD([Total Sales],'Calendar'[Date]) on page 119

- Only filter applied for 'Calendar' [Calendar Year] = 2018

- The formula is correct however I can't get the 'Total Sales Quarter to Date' to show the running total for each quarter in 2017.

- What might be the issue here? Am I using the wrong filters? Wrong matrix fields?

- Note: TOTALMTD() worked fine when I used 'Calendar'[DayNumberofMonth] in the matrix row.

 Matrix snippets:

 

 Measure formula:

Thanks in advance!

[johnt75]

It looks like the problem is either in the calculation for Q1 for [Total Sales] or in Q2 for [Total sales previous quarter]. Which one is the correct answer and which one is wrong ?

[johnt75 · Accepted solution]

The measure is working correctly. If you put one of the month columns in the matrix you will see the total qtd grow until it resets at the start of the next quarter.

[RokuCap]

Thanks for the reply. I replaced 'Calendar'[Calendar Quarter] with the column 'Calendar'[DayNumberofMonth] and that worked. Using the 'Calendar'[MonthName] as a filter, I could see the TOTALQTD increasing from Jan --> March then resetting at April.

I think I misunderstood how these functions worked. I expected the TOTALQTD to grow across all quarters in a year (e.g. Q4>Q3>Q2>Q1 like below). But the TOTALQTD function only provides a sum within a specified quarter before resetting for the next quarter. Is this correct?

[johnt75]

Yes, that's correct. To see the figures growing quarter on quarter you would use the TOTALYTD function instead.

[RokuCap]

Hi John, instead of creating a new thread, I thought I'd add this here.

Total Sales Previous Quarter = CALCULATE([Total Sales],PREVIOUSQUARTER('Calendar'[Date]))

I'm expecting the [Total Sales Previous Quarter] value to match the value from the [Total Sales] column, one row above. [Total Sales]{1} does not match [Total Sales Previous Quarter]{2}.

Note: I'm not sure if the above syntax is correct for referring to the value in 'Total Sales' Column and row {1} or row {2}. Feel free to correct me!

Do you know why this is?

[johnt75]

Is your calendar table marked as a date table ?

[RokuCap]

Thanks for the quick reply! It wasn't before. I marked it as a data table but the 'Total Sales'[Q1] still doesn't match 'Total Sales Previous Quarter'[Q2].

The formula is correct per the book. I wonder if it has something to do with the filters/filter context?

^ after marking the 'Calendar' table as a date table and selecting the [Date] column.

[RokuCap]

A quick update:

The reason formula: CALCULATE([Total Sales],PREVIOUSQUARTER('Calendar'[Date])) didn't work is because I had a slicer from another matrix unintentionally interacting with this matrix. Something I hadn't considered. Lesson learned!