Hi,

I've a table which contains a record for each day an employee is employed:

In the cube  I've create a measure which is a sum of the field "Employed"  which provides me the number of employees that are employed for a given date (lowest level of my time dimensions which are YEAR-MONTH-DATE)

The problem is that the number aggregating on YEAR and MONTH which provides me with wrong figures at these levels - So how can create a calculated measure (maybe with Scope) that only show the Ultimo Numbers at the lowest level when I am browsing on the Month or Year level. For example if I am on the Year level I only want it to sum on the date 31-12-xxxx and if I'm at the month level(for example July 2012) it should show me the sum of 31-07-2012 which are the last level for the given month.

Any help are appriciated

  

• Edited by HCMJ 20 hours 7 minutes ago

Hi,

change the aggregationtype for the measure from SUM to LASTNONEMPTY or LASTCHILD, whatever u need.

Can you provide an example of this ?

(we only have Std edition of SQL server)

Thanks

ok,

LASTNONEMPTY is NOT available in Standard edition. There might be an example on the web how lastnonempty feature can be done without enterprize or bi version.

Hi HCMJ,

In this scenario, since you are in Standard Edition, you need to custom a LASTNONEMPTY measure in your cube. You can use TAIL() and ISEMPTY() to achieve this goal. Please try expression like:

CREATE MEMBER CURRENTCUBE.[Measures].[LastNonEmpty STD] 
AS ([Measures].[Employeed], 
   tail(nonempty(--DateRange--, [Measures].[Employeed]),1).item(0)) ;

There are many other ways to achieve LASTNONEMPTY(), please refer to links below:

Getting the last non empty value

Last Ever Non Empty a new, fast MDX approach

Regards,