Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

MTD Measures Not Working

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:

 

policyNumberinceptionDate
BPP-2014-5481Tuesday,October 2, 2018
BPP-2014-5376Wednesday, October 3, 2018
BPP-2015-7130Wednesday, October 3, 2018
BPP-2015-7135Wednesday, October 3, 2018
BPP-2018-1121Thursday, October 4, 2018
BPP-2015-7136Friday, October 5, 2018
BPP-2019-1616Tuesday,October 1, 2019
BPP-2016-7654Tuesday, October 1,2019
BPP-2014-5481Wednesday, October 2,2019
BPP-2015-8258Wednesday, October 2,2019
BPP-2016-9318Thursday, October 3,2019
BPP-2015-7477Friday, 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!
Need Help
  • elxinside's avatar
    elxinside
    6 years ago

    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.

     

2 Replies

  • elxinside's avatar
    elxinside
    Frequent Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.