Cannot convert value " of type text to date

[Anonymous]

Hi PBI Heroes,

I don't understand why I am getting error on the below measure when it could work on other dimensions coming from the same table at the other charts but not the last one. The relationships and link with my Calendar table shouldn't be an issue since other charts are working.

My measure is expressed as below:

LY Prev Mth =

Var MaxDate = MAX('Calendar'[Date])

Var EndDate = FORMAT(EOMONTH(MaxDate , -13) , "MM/DD/YYYY")

Var StartDate = DATE(YEAR(MaxDate)-1,MONTH(MaxDate)-1,1)

Var Final = CALCULATE(SUM('Dimensions'[QTY]), DATESBETWEEN('Calendar'[Date],StartDate,EndDate))

return Final

I read through a few similar threads but the situation seems different.

Appreciate some insights on this. Thank you!!

Regards

Shao

[johnt75 · Accepted solution]

You're using the FORMAT function, which returns text not a date. Try

LY Prev Mth =
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR EndDate =
    EOMONTH ( MaxDate, -13 )
VAR StartDate =
    DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ) - 1, 1 )
VAR Final =
    CALCULATE (
        SUM ( 'Dimensions'[QTY] ),
        DATESBETWEEN ( 'Calendar'[Date], StartDate, EndDate )
    )
RETURN
    Final

[Anonymous]

Hi Johnt75

Thank you. It works!

However just for my understanding, why are the other charts able to work if the format function returns text?

Regards

Shao