Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Measure formula:
Thanks in advance!
Solved! Go to 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.
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 ?
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.
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?
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?
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?
^ 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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |