cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

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?
4 REPLIES 4
Highlighted
Super User II
Super User II

Hey @dpeacock 

try this

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

Regards,

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted

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.

Highlighted

Hey @dpeacock ,

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Community Support
Community Support

Hi @dpeacock ,

 

v-lionel-msft_0-1600683935770.png

 

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.

 

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors