Using date functions with a Day dimension as an argument to the function

Hi Kevin,

It’s definitely a tricky one because you’re mixing a lot of things here. I’ve made it work in the past, so this is definitely doable.
Starting with your destination, you should try to end up with a date in your transaction. To do this, let’s explore two options:

Using DATE or DATEVALUE

The functions DATE and DATEVALUE allow you to turn respectively numbers and strings into a date. 

Looking at the examples of DATEVALUE, I think we could just use your default name

So in your transaction, you could simply use DATEVALUE(Transaction.’EOP Date’.Name, “yyyy-MM-dd”)

I am adding .Name here because we want to work with the text value, not the item of your dimension EOP Date.

The second option is about importing directly the date as a date-formatted property in your EOP Date dimension.

Currently, you’re importing new items by mapping them to a unique Name property that is text-formatted. You could add another Date date-formatted property, and import your transaction’s EOP Date property using that property, by clicking on the cog in the import configuration and choosing the date instead of the name.

This might look like a more abstract solution though, so it’s up to you. The first option maps the text to a date using a formula, the second one using the import mapping functions. The best one to use would be the one you’re most comfortable with.

Hope this helps!
François