How to get MAX date for currency conversion across multiple periods using MDX

Question

Summary

I'm new to MDX but have been asked to enhance currency conversion functionality in a cube. The problem itself seems simple.

For any given selected period, get the last date, and use that as the Reporting date for FX currency conversion.

For example, user selects periods Jan 2018 through to Oct 2018. The last date would be 31-Oct-2018.

The currency conversion is many to one reporting currency so we would take rates from the 31-Oct-2018.

Methods Tried

I have tried various methods to get the last date using:

MAX (with and without existing)
ClosingPeriod
Separate FX Rate Measure Group and Date Dimension

These work fine if the user selects one period and the calculation is only for that period.

However the problem is that across multiple periods (think YTD or LTD calculations) then end rate is taken from each month.

For example, each line item here in the corresponding month would be converted using the rate at period value for that month (Jan 7.285) rather than using the Oct 2018 value of 7.027.

Period | Rate at Period | LastDate | LastDateExisting

Jan 2018 | 7.285 | 20191231 | 20180131

Feb 2018 | 7.273 | 20191231 | 20180228

Mar 2018 | 7.275 | 20191231 | 20180331

Apr 2018 | 7.201 | 20191231 | 20180430

May 2018 | 7.146 | 20191231 | 20180531

Jun 2018 | 7.118 | 20191231 | 20180630

Jul 2018 | 7.116 | 20191231 | 20180731

Sep 2018 | 7.102 | 20191231 | 20180930

Oct 2018 | 7.027 | 20191231 | 20181031

Example Code

Here is an example of the MAX method I have found in various MDX cookbooks to get the last Date. I am assuming that due to the CurrentMember I am getting results per period. I realise this is normal MDX behaviour but due to the requirement would need to break this.

The MAX example without existing is the closest as it returns the same date but I am not sure if this can be filtered for the actual closing period easily?

WITH

MEMBER Measures.[LastDate] AS

MAX( [Accounting Date].[Year – Quarter – Month – Date].[Date].MEMBERS,
IIF( [Measures].[Rate at Period] = 0,
NULL,
[Accounting Date].[Year – Quarter – Month – Date].CurrentMember.Member_Key
)
)

MEMBER Measures.[LastDateExisting] AS

MAX( EXISTING [Accounting Date].[Year – Quarter – Month – Date].[Date].MEMBERS,
IIF( [Measures].[Rate at Period] = 0,
NULL,
[Accounting Date].[Year – Quarter – Month – Date].CurrentMember.Member_Key
)
)

Expected Results

I would like to see the last date column as all ‘20181031’ as below.

Period | Rate at Period (overridden) | LastDate

Jan 2018 | 7.027 | 20181031

Feb 2018 | 7.027 | 20181031

Mar 2018 | 7.027 | 20181031

Apr 2018 | 7.027 | 20181031

May 2018 | 7.027 | 20181031

Jun 2018 | 7.027 | 20181031

Jul 2018 | 7.027 | 20181031

Sep 2018 | 7.027 | 20181031

Oct 2018 | 7.027 | 20181031

Any help appreciated and happy to provide more information should you need it.

I believe I may have found a solution but am waiting to get back to this problem to test it fully. It uses the descendants function looking at all leaves for the periods selected. It does not of course work over several years but I don't believe this will be a problem for the users: MAX( TAIL( DESCENDANTS( NONEMPTY( EXISTING [Accounting Date].[Year - Quarter - Month - Date].MEMBERS, [Measures].[Rate At Period]) , [Accounting Date].[Year - Quarter - Month - Date].[Year], LEAVES) , 1).item(0).item(0) , [Measures].[Rate At Period]) Any better solutions? :) — Thinster

whytheq whytheq · Accepted Answer · 2019-05-23T19:15:47

Looks like you already figured this bit:

WITH
MEMBER Measures.[LastDate] AS
MAX( 
  [Accounting Date].[Year – Quarter – Month – Date].[Date].MEMBERS,
  IIF( 
     [Measures].[Rate at Period] = 0,
     NULL,
     [Accounting Date].[Year – Quarter – Month – Date].CURRENTMEMBER.MEMBER_KEY
   )
)

So to get the Rate at Period (overridden) you can just use the date you've found and make a measure using a tuple - only slight change is that you will need to add the LastDate member to a hierarchy other than Measures - you can take your pick: in our cubes I usually pick the Langauges hierarchy as it is hardly ever used:

WITH
MEMBER <OtherHierarchy>.[LastDate] AS
MAX( 
  [Accounting Date].[Year – Quarter – Month – Date].[Date].MEMBERS,
  IIF( 
     [Measures].[Rate at Period] = 0,
     NULL,
     [Accounting Date].[Year – Quarter – Month – Date].CURRENTMEMBER.MEMBER_KEY
   )
)
MEMBER Measures.[Rate at Period (overridden)] AS
( 
  <OtherHierarchy>.[LastDate],
  [Measures].[Rate at Period]
)