Forum Discussion

julsr's avatar
julsr
Continued Contributor
1 year ago
Solved

DatesMTD error when using all the data in my database

Hello everyone! I'm using the datesMTD calculation to get Month To Date values. First, I used it with one year of data, and it worked. Now, I've tried using it for 13 years of information, and I'm getting the following error:

 

What could be wrong with it? What should I fix on my code (find it below) or in my database to fix this error? (both tables are joined by a Key that generates a many-to-many relationship)

 

TotalMTDValue =
CALCULATE(
    SUM(database[amount]),
    REMOVEFILTERS(DatesTable[Date],DatesTable[month],DatesTable[year],DatesTable[month_name]),
    DATESMTD(DatesTable[Date]),
    TREATAS(
        VALUES(DatesTable[account_t]),
        database[type_account]
    )
)
Need Help
Show and Tell
Tips and Tricks
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    julsr  Try using below DAX

     

    DAX
    TotalMTDValue =
    CALCULATE(
    SUM(database[amount]),
    DATESMTD(DatesTable[Date])
    )

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    julsr  Try using below DAX

     

    DAX
    TotalMTDValue =
    CALCULATE(
    SUM(database[amount]),
    DATESMTD(DatesTable[Date])
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi julsr ,

     

    At my knowledge, using time intelligence functions requires the date table to be continuous and non-repeating date range.

    Does your date table contain duplicate dates? If so, please try using the calendar() function to create a date table.

     

    Best Regards,

    Wearsky