TOTALMTD date parameter is causing incorrect results

[MattRasmussen]

I want to get the MTD and YTD amount of hours worked from our table containing employee hours worked for a given week.  I'm new to Power BI and DAX and can't get the TOTALMTD or TOTALYTD functions to work right to save my life.  The documentation shows the syntax as:

TOTALMTD(<expression>,<dates>[,<filter>])

It explains that the dates parameter is "A column that contains dates." Okay, but that's so vague it's not helpful.  

I have a calendar fact table that is joined to the employee hours worked table.  When I use the calendar date column in our calendar fact table, as most people do in their examples, the function returns nothing (but does not give any errors):

Hours_MTD = TOTALMTD(SUM(Time_Recorded[Total Hours Applied]),DIM_CALENDAR_CURRENT[CALENDAR_DATE])

When I use the week ending date column in the employee hours worked table, the function returns numbers that are impossibly high and verified as incorrect:

Hours_MTD = TOTALMTD(SUM(Time_Recorded[Total Hours Applied]),Time_Recorded[Week Ending Date])

It seems so simple, but why is the function not working right?  I can't find any explanations of what column should be used for the date parameter other than the calendar fact table.

[amitchandak]

@MattRasmussen , The date you are joining with the date of date tbale , is having a timestamp. Means time other than 12 AM.

You need create a date first

Date new = datevalue([Date])

Refer video and blog Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI

:https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

[MattRasmussen]

Thank you for looking at my issue, @amitchandak.  I watched your video to understand what you mean but our calendar table has no timestamp:

However, the Week Ending Date column of the employee hours worked table is a data type of Date (not Date/Time) and it has no time in the column.  I cannot select a format with a time for the column.  Just to clarify, here is the join between the calendar and the employee hours worked table, note that the Week Ending Date column does not have the calendar icon next to the name like the calendar_date column:

 

Does the data type of Week Ending Date affect the time intelligence functionss?  Any other ideas why these functions do not work for me?