Search code examples
mdxcube

multidimensionel cube: sum on filtered fact

I have a fact_dueAnalysis table which contains all my customers outstanding. The table is built with a Date Reporting, so when I pick a specific Date Reporting date, I can see all customers outstanding for that specific day.

Now I want to let my users use a date hierarchy so they can select e.g. week 39 and get all outstanding for the LAST day of that week (I will always use the last date in the hierarchy my users have selected).

I have made the following script:

([Measures].[Due Amount],[Date Reporting].[Year -  Week -  Date].[Week].members) =
sum(generate(tail(
DESCENDANTS([Date Reporting].[Year -  Week -  Date].[Week],, leaves),1),1
)
,[Measures].[Customer Due Amount]);

I am here trying to get the latest (tail) date (leave) on my Date Reporting and then sum the Customer Due Amount and get the result in a new measure called Due Amount.

When the users select a specific date, it does work, but when they select a week I get a #VALUE as result.

How should I create this correct?

Solution

  • Here is my end result:

    ([Measures].[Due Amount],[Date Reporting].[Year -  Week -  Date].[Week].members) =
SUM(TAIL(DESCENDANTS([Date Reporting].[Year -  Week -  Date].CURRENTMEMBER,
 [Date Reporting].[Year -  Week -  Date].[Date]),1)
,[Measures].[Customer Due Amount]);

    This will do what I want:

    Get the latest date in a selected week and sum the Customer Due Amounts and present it as Due Amount.