MDX Show QtyOnHand based on Time Member selected

[BobbyPolo]

Scenario:

I have the measures Sales and QtyOnhand for items by month.

When I view the data for June, I see total sales for the items through June, which is perfect.

However, I also see the QtyOnHand is aggregated for all the items (which is great) and for all the months through June (which is bad).

For example
Item Sales QtyOnhand Month
1 5.50 4 January
2 0.00 2 January
1 1.00 6 February
2 3.00 2 February

Right now if I look at February, I get

Sales: 9.50 Qty On Hand: 14

What I want is Sales: 9.50 Qty On hand: 8

So I want to aggregate sales as it is, but I only want to aggregate Qty On Hand for the time period I am looking at.

Any suggestions of paths to take or options I have?

Thank you so much!!!!

 

[BobbyPolo]

Ok, I tried something like this:

IIF(IsLeaf([Inv_Time]),sum({[Inv_Time]},[Measures].[Inv Value]),IIF(IsGeneration([Inv_Time],1),sum({[Inv_Time].lastchild},[Measures].[Inv Value]),sum({[Inv_Time].lastchild.lastchild},[Measures].[Inv Value])))

It uses Inv Value instead of Qty, but it is the same concept.

Is there an easier or more efficient way to do this?

Thanks!

 

[BobbyPolo]

Here is my new MDX function:

(Measures.[Inv Value], ClosingPeriod([Month]))

In case anyone was in need of a good solution.