Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
2 years ago
Solved

MTD vs YTD

Hi,

Can anyone explain why and what is happening with the following DAX please?

The YTD measure works fine but the same logic when applied to the MTD measure fails:

 

This Works =

Assessment Closed Cases YTD = 
CALCULATE(
    TOTALYTD(COUNT('Cases'[Case Number]),'Cases'[Resolution Date],"31/03"),
    'Cases'[statecode] = "Resolved",
    'Cases'[User Team Name] = "Assessment")

 

This Fails = 

Assessment Closed Cases MTD = 
CALCULATE(
    TOTALMTD(COUNT('Cases'[Case Number]),'Cases'[Resolution Date],"31/03"),
    'Cases'[statecode] = "Resolved",
    'Cases'[User Team Name] = "Assessment")

FYI: I'm having to count the actual Resolution Date column because the Cases [Resolution Date] column is not linked to my Date table because the main active relationship uses Cases [Created On] date instead. I don't this has anything to do with this error but I thought I'd mention it in anycase!
Thanks

Need Help
Show and Tell
  • DatawithDinesh's avatar
    DatawithDinesh
    2 years ago

    The TOTALYTD function calculates the year-to-date total, and it allows you to specify an optional end date (like "31/03"). This date parameter tells the function to compute the year-to-date total up to that specific date.

     

    The TOTALMTD function, on the other hand, is designed to calculate the month-to-date total. Unlike TOTALYTD, it doesn't typically require or accept an end date in the same way. The issue arises because the "31/03" date string doesn't match the expected date format or handling in TOTALMTD.

    To correct the issue with the TOTALMTD function, you should remove the "31/03" end date from the calculation:

    Assessment Closed Cases MTD = 
CALCULATE(
    TOTALMTD(
        COUNT('Cases'[Case Number]), 
        'Cases'[Resolution Date]
    ),
    'Cases'[statecode] = "Resolved",
    'Cases'[User Team Name] = "Assessment"
)

     

     

2 Replies

  • DatawithDinesh's avatar
    DatawithDinesh
    Icon for Resolver II rankResolver II
    2 years ago

    The TOTALYTD function calculates the year-to-date total, and it allows you to specify an optional end date (like "31/03"). This date parameter tells the function to compute the year-to-date total up to that specific date.

     

    The TOTALMTD function, on the other hand, is designed to calculate the month-to-date total. Unlike TOTALYTD, it doesn't typically require or accept an end date in the same way. The issue arises because the "31/03" date string doesn't match the expected date format or handling in TOTALMTD.

    To correct the issue with the TOTALMTD function, you should remove the "31/03" end date from the calculation:

    Assessment Closed Cases MTD = 
CALCULATE(
    TOTALMTD(
        COUNT('Cases'[Case Number]), 
        'Cases'[Resolution Date]
    ),
    'Cases'[statecode] = "Resolved",
    'Cases'[User Team Name] = "Assessment"
)