Forum Discussion
TOTALYTD works, TOTALMTD does not - same data
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.
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
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
7 Replies
- alanhodgson
Solution Supplier
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
Let me try and obfuscate my data and post the pbix.
I posted my file to one drive.
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
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
