Forum Discussion
TOTALMTD using previous months total because data is not available for current month
Hello everyone!
I am using TOTALMTD in a SWITCH to calculate TOTALPAID MTD with a Slicer for All Vendors and individual vendors. This seems to work accurately with the exception of 1 specific vendor. When looking at the dataset, I noticed that all but one vendors have at least one record for the current month. The vendor that has no record for this month is the one that is returning the wrong result.
For example:
| Vendor | Amount | Date Paid |
| ABC | 1,000.00 | 3/2/2025 |
| ABC | 1500.00 | |
| DEF | 1500.00 | 3/5/2025 |
| DEF | 3000.00 | 2/6/2025 |
| DEF | 2500.00 | |
| XYZ | 2000.00 | |
| XYZ | 6000.00 | 2/6/2025 |
| XYZ | 3000.00 | 2/6/2025 |
Using the above Data, I should be returning the following MTD values for March 2025 (MTD):
| ABC | 1000.00 |
| DEF | 1500.00 |
| XYZ | 0 |
| All Vendors | 2500.00 |
However, what I am returning is the following:
| ABC | 1000.00 |
| DEF | 1500.00 |
| XYZ | 9000.00 |
| All Vendors | 2500.00 |
It apears that because there is no data for the current month (March), TOTALMTD is evaluating the previous month (Feb) and returning those values when only that vendor is selected. However, when all vendors are selected, it is returning the correct values.
My question: is there a way to return zero even if the date column used has no data for the current month rather than the TOTALMTD function evaluating for the last known month in a data set?
Here are the measures I am using:
Hi Anonymous
When using time-smart functions, creating a corresponding calendar table is a better option.
But in your case, you can also just try the following measure:
Period Paid = SWITCH([Selected Period], 1, TOTALMTD([Paid], DATESMTD(ALL('Table'[Date Column]))), 2, TOTALQTD([Paid], DATESQTD(ALL('Table'[Date Column]))), 3, TOTALYTD([Paid], DATESYTD(ALL('Table'[Date Column]))), 4, TOTALYTD([Paid], PREVIOUSYEAR(DATESYTD(ALL('Table'[DateColumn])))), 5, [Paid] )Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
Thank you! I do have a corresponding Date calendar and I apologize for not including that information in the original post. However, I have updated the "Paid" measure with "DATESYTD(ALL... " as you stated in your post and this seems to have worked! The only thing is that it is displaying "BLANK" and I need it to display "0".. any suggestions? I have not yet tried anything but thought I would ask anyways 🙂
6 Replies
- xifeng_L
Super User
Hi Anonymous
When using time-smart functions, creating a corresponding calendar table is a better option.
But in your case, you can also just try the following measure:
Period Paid = SWITCH([Selected Period], 1, TOTALMTD([Paid], DATESMTD(ALL('Table'[Date Column]))), 2, TOTALQTD([Paid], DATESQTD(ALL('Table'[Date Column]))), 3, TOTALYTD([Paid], DATESYTD(ALL('Table'[Date Column]))), 4, TOTALYTD([Paid], PREVIOUSYEAR(DATESYTD(ALL('Table'[DateColumn])))), 5, [Paid] )Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
Thank you! I do have a corresponding Date calendar and I apologize for not including that information in the original post. However, I have updated the "Paid" measure with "DATESYTD(ALL... " as you stated in your post and this seems to have worked! The only thing is that it is displaying "BLANK" and I need it to display "0".. any suggestions? I have not yet tried anything but thought I would ask anyways 🙂
- xifeng_L
if want to display 0, you can use +0, such as:
TOTALMTD([Paid], DATESMTD(ALL('Table'[Date Column]))) +0
- lbendlin
Your data model seems to be lacking a calendar table (required for time intelligence calculations and a vendor dimension table (required for scenarios where you need to report on things that are not there).
Thank you for taking the time. I do have a calendar table and vendor table and apologize for not stating that in the original post. I really believe the issue is with how TOTALMTD selects the data.. if a record for the current month does not exist, it will go back to the last month that does exist in the data set and display that data rather than using the current month as it relates to todays date.
