LAst order date

---

I have a fact table that contains amongst other facts, the orderdate. There is 1 line per order.
I would like to show for each "sold from BP", the last order date, like i do in the example below for the "Transaction amount".
Can anyone give me a hint on how the MDX syntaxt should be?

select NON EMPTY {[Measures].[Transaction amount (EURO)]} ON COLUMNS,
NON EMPTY {[sold from BP.BP(ID)].[All sold from BP(ID)]} ON ROWS
from [finalizedtransactionscube]
where [Transaction type.Transaction type].[PCI+PCC]

Hopefully this gives you an idea on how to proceed with this:

Code Snippet

withmember [measures].[x] as

MAX(

EXISTS(

[Ship Date].[Date].[Date].Members,

{Product.SubCategory.CurrentMember},

'Internet Sales'

),

[Ship Date].[Date].CurrentMember.MemberValue

)

select

[x] on 0,

TAIL(Product.SubCategory.SubCategory.Members,10) on 1

from [Adventure Works]

I use TAIL on Axis(1) to just limit the data returned. The calculated member at the top is where all the work is performed.

What this says is build a set of ship date members associated with the current product subcategory based on the relationship between these two dimensions as defined in the Internet Sales measure group. Take the max value for ship date from this set and return that as the measure's value. The MAX() function was the tricky part.

Good luck,
Bryan