MTD Measures Not Working

[Anonymous]

Hi All,

I am currently building a report that needs to show the MTD data for the current year and for several years in the past. I am unable to get even the "Month-To-Date Total" quick measure to work. Here is some sample data for the two columns I am working with:

policyNumber	inceptionDate
BPP-2014-5481	Tuesday,October 2, 2018
BPP-2014-5376	Wednesday, October 3, 2018
BPP-2015-7130	Wednesday, October 3, 2018
BPP-2015-7135	Wednesday, October 3, 2018
BPP-2018-1121	Thursday, October 4, 2018
BPP-2015-7136	Friday, October 5, 2018
BPP-2019-1616	Tuesday,October 1, 2019
BPP-2016-7654	Tuesday, October 1,2019
BPP-2014-5481	Wednesday, October 2,2019
BPP-2015-8258	Wednesday, October 2,2019
BPP-2016-9318	Thursday, October 3,2019
BPP-2015-7477	Friday, October 4, 2019

I need the distinct count of policyNumbers for this month-to-date and MTD for October of 2018, October 2017, October 2016 etc. etc.

The "Month-To-Date Total" give me a result of "blank":

Count of policyNumber MTD =
IF(
    ISFILTERED('working_file'[inceptionDate]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALMTD(
        COUNTA('working_file'[policyNumber]),
        'working_file'[inceptionDate].[Date]
    )
)

This measure gives me a completely incorrect result far below the actual distinct count:

MTD Policies = 
TOTALMTD ( DISTINCTCOUNT(working_file[policyNumber] ), working_file[inceptionDate])

This measure gives me the correct total FOR THE ENTIRE MONTH and not the MTD:

Current MTD FINAL =
CALCULATE(
    DISTINCTCOUNT('working_file'[policyNumber]),
    DATESBETWEEN(working_file[inceptionDate],DATE(YEAR(TODAY()), MONTH(TODAY()), 1),TODAY())
)​

Same as the code above, this code gives me the total for the entire month and not the MTD one year prior:

1 year MTD FINAL = 
CALCULATE(
	DISTINCTCOUNT('working_file'[policyNumber]),
	DATEADD(DATESBETWEEN(working_file[inceptionDate],DATE(YEAR(TODAY()), MONTH(TODAY()), 1),NOW()),-1,YEAR)
)

 I've spent a great deal of time on this and have yet to come up with a solution.

Would greatly appreciate some guidance!

@v-juanli-msft 

[elxinside · Accepted solution]

Hi @Anonymous ,

from what you say I don't understan what you are using as a time reference. In order to have time intelligence functions to work you need to have a "calendar table" with all the dates in the time interval you are considering.

You can easily build it with calendar() function. https://docs.microsoft.com/en-us/dax/calendar-function-dax

Once you have it, you should connect the date in the calendar table to your inceptiondate and you should use the date in the calendar table as reference for time intelligence dax functions.

[Anonymous]

Thank you, @elxinside! I am fairly new to Power Bi and did not realize I needed to have that calendar table for time intelligence functions. This not only fixed my MTD measures but I was also able to fix my 12 Month Rolling measures & YTD measures.