Using an "actual date" in a formula for a calculated column

[Anonymous]

I thought this would be straight forward, but it's not.  I need to put an actual date into a formula as a filter.  I don't know the format to use for dates in this instance and I'm thinking that must be the problem.

This works when I'm filtering by FY:  

UFY19 = SUMX( FILTER(RELATEDTABLE(Sales),Sales[FY]=2019),Sales[Units])

But this does not work when I filter by InvDate:

UFY19MX = SUMX( FILTER(RELATEDTABLE(Sales),Sales[InvDate]>04/06/2020),Sales[Units])

It's giving me all of the units rather than the portion that fall after the date.    As a double check, I used the side-bar filter for that visual and put "after 04/06/2020" in the advanced filter for the InvDate field and got the correct amount, so I know that the date field is formatted as a date field and can be filtered as such.

I'm guessing that the way I entered the date into the formula is wrong.  Has anyone come across this?

[v-lionel-msft]

Hi @Anonymous ,

I don’t understand why you need the RELATEDTABLE() function. If you remove this function, what result will be returned by the measure?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Anonymous 

try this

UFY19MX = SUMX( FILTER(RELATEDTABLE(Sales),Sales[InvDate]>"04/06/2020"),Sales[Units])

Regards,

Tom 

[Anonymous]

Thanks for the suggestion but it didn't like that.  It threw this error at me - look like putting quotation marks around it makes DAX think it's text.

DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[TomMartens]

Hey @Anonymous ,

sorry, my statement did miss an essential part, you have to wrap the function DATEVALUE(...) around the string.

Using DATEVALUE has a caveat, read this article: https://docs.microsoft.com/en-us/dax/datevalue-function-dax

You might consider using text operations to extract the YEAR, MONTH, DATE part from the string, convert these strings into a DATE using the DATE function. 

Regards,

Tom