Last non-empty child over period using MDX

Question

[EDIT: Example was not entirely correct]

I've been pulling my hair out on this MDX issue... I am making a cube concerning billing.

I have 2 (relevant) dimensions: budgetbill and month
And one measure: Amount

budgetbill date         Amount
1548632    2012-11-04   50
1548632    2012-11-23   40  <--
1548632    2012-12-16   70  <--

1724687    2012-10-02   120
1724687    2012-10-23   170
1724687    2012-10-89   200  <--

TOTAL                   310

I also have a Date Hierarchy [BB UpdateDate]

Year - Quarter - Month - Week - Date

So I need to have the last amount per Month, over the budgetbill.
In the above example 40 + 70 + 200 = 310

One of the mdx code snippets I've tried

with member [Measures].[test] as
Sum(Tail(nonempty(Descendants([BB UpdateDate].[BB UpdateDate Hierarchy].currentmember, [BB UpdateDate].[Month])
,[Measures].[Amount]), 1),[Measures].[Amount])

select [Measures].[test] on columns
,nonempty([BB UpdateDate].[Month]) on rows
from [BudgetBill]
where {[BudgetBill].[BudgetBillNr].&[1548632],[BudgetBill].[BudgetBill].&[1724687]}

Gives 120 + 170 + 200 = 490 --> Not correct... This is just a (close) example but I tried so many things!

Thanks in advance for any help!!

Hi and thanks. I also believe I'd have to use Tail() to get the result I want but the problem is more how to use Tail :) In the above example you can see I started experimenting with it but without success. Thank you in advance — laurens
Lately I don't really get any answers any more here at Stackoverflow... Experts have left ? — laurens

Tom Martens Tom Martens · Accepted Answer · 2013-05-23T21:27:45

I think this will do the trick

with

member [measures].[maybe] as
sum(
generate(
    Descendants([date].[BB Update Hierarchy].currentmember, [date].[BB Update Hierarchy].[month])
    ,tail(
        nonempty(Existing [date].[BB Update Hierarchy].[date], [Measures].[amount])
    ,1)
)
,[Measures].[amount])

Hope this helps

Last non-empty child over period using MDX

1 Answers