Calculated Column does not filter Time

[MatteoColzani]

Hi,

I am creating a financial report based on a tabular cube and I am having a problem trying to extract the last Rate of a specific Instrument before the user selected period (<= Period).

I have the following tables:

 - RatingAssignment : this fact tables contains the link between the Instrument (SyId) and the Rate (RatingId) for all the days

- FactInstrument: it contains the Return values for each Instrument

- Instrument: dimension table of the instrument

What I need  to create is a bar chat with the Return values by Rating 

In order to get the correct Rates for the selected period, I built the following Calculated Column in the Instrument Table

=CALCULATE(
     VALUES(RatingAssignment [RatingId]);
     TOPN(1;
       CALCULATETABLE( 
          FILTER(RatingAssignment;RatingAssignment[RatingId]<>Blank());
          FILTER(ALL (TIME[Date] );TIME[Date]<=MAX(TIME[Date]))
         );
       RatingAssignment [Date];DESC)
     )

The Calculated column retrieves the Rating but does not consider the User Selected Period.

Anyone could you help me to understand how to filter this calculated column for the specific period?

Thanks you very much for your help!

[v-chuncz-msft · Accepted solution]

@MatteoColzani,

Values in a calculated column are fixed. They are an immutable result for each row in the table. You'll need to create a measure.