TOTALYTD works, TOTALMTD does not - same data

[mc625569]

Hello. 

I'm having issues using the TOTALMTD function.  When I use the exact same data with the TOTALYTD function my data works normally. 

I have 2 tables. 

Table 1 :INSPECTIONS Column 1 = GUID - a 32 bit guid

Column 2 = DATE - a Date colum that has a date value that corresponds with when that guid was created in the system. 

Table 2 : DATETABLE 

This is a date dimension table I created via the instructions here. 

http://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps...

Here are the 2 measures I am using to try and count all the rows in the "Inspections" table that are in the current month , aka January 2017. I know for a fact this should be 22 records. 

My formulas are as follows:

THIS WORKS and shows 22 in the dashboard card

M_YTD_Inspections = TOTALYTD(DISTINCTCOUNT(Inspections[inspGUID]),DateTable[Date])

This Shows "(Blank)"' when I run it on the same data

M_MTD_Inspections = TOTALMTD(DISTINCTCOUNT(Inspections[inspGUID]),DateTable[Date])

If I display all my records, showing their GUID, their date and their corresponding link to the date in the date table I see all the data so I know the relationship between my inspections table and my date table are working , at least as far as I can tell. 

Am I misunderstanding how the TOTALMTD function works? How is the YTD function different from the MTD function? 

Thanks, 

Mike

[alanhodgson]

Hey @mc625569,

Can you provide some more information on your situation?

I tested the 2 formulas and they worked correctly on my data set (Excel file). Make sure there are no slicers/filters on the table you are working on. Also, make sure there formulas are used to make measures not columns. See below:

Hope this helps,

Alan

[mc625569]

I posted my file to one drive. 

Link to PBIX

What I'm hoping ot create is the monthly total of records for the current month, so whatever the current month is, they see the total so far this month, that's it - do not need a daily running total or anything like that. 

So in this case we have 35 records in the inspection table that have a date of January 2017 - so they should see "35"

instead they see blank.  If I use the YTD instead of MTD on the same data they do in fact see "35" which is the correct number. 

I do have to create a second date field that doesn't use timestamps so I can create a relationship to the date table - you will see that in the inspections table. 

[alanhodgson · Accepted solution]

Hey @mc625569,

It is definitely behaving strange. I tested a few things and found that if I add a slicer to the page for "MonthNameLong" from the 'Date Table' and select "January", then the MTD populates correctly.  Also, you can get the MTD to populate by changing the formulas for both MTD and YTD to go off the "Inspections[DateNoTime]" column instead of the Date column from the 'Date Table'. See below:

Hope this helps,

Alan

[eerba2]

i'm having the same issue. both my QTD and YTD work as expected but the MTD has many blanks in my table. I added January to the table visual filter and that fixed it but, is there a way to built this into the measure instead of using visual filters? What is causing the MTD measure to behave diferently than QTD and YTD?

i'm using dates from a Time.Date dimension in a cube with no gap in dates.

[Matt_Alexander]

Hi @alanhodgson, @mc625569, et al

Just wondering if anyone understands why this happens? I have the exact same scenario when I base the TOTALQTD and TOTALMTD measures on the date table they don't work, when TOTALYTD does, but when I base it on the date in the fact table they do work.

Odd or am I missing something here?

Casos Nuevos YTD = TOTALYTD([Casos Nuevos];Calendario[Date].[Date]) - OK

Casos Nuevos MTD = TOTALMTD([Casos Nuevos];Calendario[Date].[Date])  - Not OK

Casos Nuevos MTD = TOTALMTD([Casos Nuevos];Solicitudes[Fecha_Solicitud]) - OK

Many thanks!

Matt

[mc625569]

Let me try and obfuscate my data and post the pbix.