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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors