Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
RokuCap
Helper I
Helper I

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

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:
RokuCap_0-1684151763701.png RokuCap_4-1684152098842.pngRokuCap_5-1684152576131.png

 

 Measure formula:

RokuCap_3-1684152050418.png

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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.

View solution in original post

8 REPLIES 8
johnt75
Super User
Super User

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
Super User
Super User

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.

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.

 

RokuCap_1-1684229603681.png

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?

RokuCap_3-1684230826530.png

 

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

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?

RokuCap_0-1684403915323.png

 

Is your calendar table marked as a date table ?

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?

 

RokuCap_0-1684405502234.png

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

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!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.