TOTALMTD using previous months total because data is not available for current month

[adentler]

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:

Paid = CALCULATE(

    SUM('Table'[Amount Column]),

    FILTER(

        'Table',

        NOT(ISBLANK('Table'[Date Column]))

    )

)

Period Paid =

SWITCH([Selected Period],

1, TOTALMTD([Paid], DATESMTD('Table'[Date Column])),

2, TOTALQTD([Paid], DATESQTD('Table'[Date Column])),

3, TOTALYTD([Paid], DATESYTD('Table'[Date Column])),

4, TOTALYTD([Paid], PREVIOUSYEAR(DATESYTD('Table'[DateColumn]))),

5, [Paid]

)

Remember, these works flawlessly for calculating these periods for ALL Vendors, as well as individually for those vendors that have a record within the current month. It is only causing a problem when a vendor does not have a record to calculate within the current month.

[xifeng_L · Accepted solution]

Hi @adentler

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~

[xifeng_L · Accepted solution]

Hi @adentler

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~

[adentler · Accepted solution]

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]

Try not to need that. Blank is better than 0. If you insist, use COALESCE.

[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).

[adentler]

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.