Date in Set Analysis

[m_perreault]

Hi All,

I have the two tables in the image below.  ID Number 1A564DDS has three TransactionDates shown in the top table and has MonthEnd Price's shown in the bottom table.  I want to write an expression that shows the Price at the time of the transaction.  I have tried using the expression Only({<[Data As Of Date] = {"$(=Date(MonthEnd(TransactionDate),'M/DD/YYYY'))"}>}[Price - Hist]) and its giving me null values.  When I hard code a date such as

Only({<[Data As Of Date] = {'11/30/2017'}>}[Price - Hist])

It gives me what I expect.  Does anyone know how to accomplish what I am trying to do?

Thanks,
Mark

[marcus_sommer]

Monthend() returned no date else a timestamp which means you need an additionally rounding like:

Only({<[Data As Of Date] = {"$(=Date(floor(MonthEnd(TransactionDate)),'M/DD/YYYY'))"}>}[Price - Hist])

Further you need to ensure that there is only a single TransactionDate is available and otherwise this call will return NULL because there is no aggregation like max(TransactionDate).

But if I look again on your screenshot it looked that you want to apply this expression on a record-level which couldn't work in this way because you used a $-sign expansion which mean that this part is globally calculated before the other parts of the expression and used there for each row.

You could try it without the $-sign expansion but it don't worked in each case but maybe  ...

Only({<[Data As Of Date] =

     {"=[Data As Of Date]=Date(floor(MonthEnd(TransactionDate)),'M/DD/YYYY')"}>}

[Price - Hist])

- Marcus