I have the following data in my cube. The measure is "daynumber of leave". I want to calculate the average duration of leave for the period 4-jan-2015 to 8-jan-2015. In this case (9+19+4) / 3 = 10,667.

DATE
EMPLOYEE	1-jan-15	2-jan-15	3-jan-15	4-jan-15	5-jan-15	6-jan-15	7-jan-15	8-jan-15
1	1	2	3	5	6	7	8	9
2	12	13	14	15	16	17	18	19
3	1	2	3	4
4	1	2	3

Which MDX statement could I use?

What are the numbers in this table and what do you mean by 'daynumber of leave'? Not sure I follow how you got the 9, 19, 4 to do an average on.

Also, what is your dimension in the cube?

Hi JohnMinkjani8,

According to your description, you want to calculate the average days of leave within a period. Right?

In this scenario, I suggest you specify a date range within a AVG() function.

CREATE MEMBER CURRENTCUBE.[Measures].[Avg] AS 
AVG(
[Employee].[Employee].currentmember * 
([Date].[Calendar].[Date].&[20150104]:[Date].[Calendar].[Date].&[20150108]), 
[Measures].[Days of Leave]
);

Reference:
Avg (MDX)

Regards,

I found a work around:

CASE

WHEN [Kalender].[Kalender].CurrentMember.LEVEL.Ordinal = 1 then /* Year */

(aggregate([Kalender].[Kalender].currentmember.lastchild.lastchild.lastchild

, [Measures].[_SumDagInVerzuim] )

-(aggregate([Kalender].[Kalender].currentmember.lastchild.lastchild.lastchild

,[Measures].[_MeldingDuur])) /* Laatste dag Meldingen aftrekken want zitten in ook in de totaal telling */

+ [Measures].[_MeldingDuur]) /

[Measures].[_Verzuimelding_DC]

WHEN [Kalender].[Kalender].CurrentMember.LEVEL.Ordinal = 2 then /* Quarter */

(aggregate([Kalender].[Kalender].currentmember.lastchild.lastchild

,[Measures].[_SumDagInVerzuim]) -

(aggregate([Kalender].[Kalender].currentmember.lastchild.lastchild

,[Measures].[_MeldingDuur]))

+ [Measures].[_MeldingDuur])

/ [Measures].[_Verzuimelding_DC]

WHEN [Kalender].[Kalender].CurrentMember.LEVEL.Ordinal = 3 then /* Month */

(aggregate([Kalender].[Kalender].currentmember.lastchild

,[Measures].[_SumDagInVerzuim]) -

(aggregate([Kalender].[Kalender].currentmember.lastchild

,[Measures].[_MeldingDuur]))

+ [Measures].[_MeldingDuur])

/ [Measures].[_Verzuimelding_DC]

WHEN [Kalender].[Kalender].CurrentMember.LEVEL.Ordinal = 4 then /* Dag */

([Measures].[_SumDagInVerzuim]) / [Measures].[_Verzuimelding_DC]

ELSE

NULL

END

Regards John