DATEDIFF "An invalid numeric representation of a date value was encountered" Error

Question

I am using the following formula in Power BI to find how many months between today and a date that is stored in a table:

MonthsSincePaid = IF(
    ISBLANK(SUM('table name'[check_date_revenues])),
    BLANK(),
    DATEDIFF(
        CALCULATE(SUM('finance_energy_link_mat_api revenues'[check_date_revenues].[Date])),
        TODAY(),
        MONTH
    )
)

This is what the table looks like:

I get this error: MdxScript(Model) (6, 5) Calculation error in measure 'table name'[MonthsSincePaid]: An invalid numeric representation of a date value was encountered.

I filtered the visualization to just one date value to troubleshoot and am still getting the same error.

If I am understanding the error correctly, it is finding an invalid value in the check_date_revenues column, but that date looks fine to me. What am I misunderstanding? And, what do I need to change?

Answer 1

Try this. Make check_date_revenues column as date data type.

MonthsSincePaid = IF(
    ISBLANK(SUM('table name'[check_date_revenues])),
    BLANK(),
    DATEDIFF(
        'finance_energy_link_mat_api revenues'[check_date_revenues],
        TODAY(),
        MONTH
    )
)