Written by

Cofounder at snext

Question Blaise ZARKA · Jun 8, 2016

How to get the total sum of accounts until a certain date?

In DeepSee, having a customer account cube. The cube includes a time dimension AccountCreationDate.

How do you create a calculated measure to get the total number of accounts from the beginning to a certain date in abscissa ?

I tried many things including this:
AGGREGATE(%TIMERANGE([AccountCreationDate].[H1].[MonthYear].&[190001],[AccountCreationDate].[H1].[MonthYear].CURRENTMEMBER) ,[Measures].[%Count])

But I don't get the right result because the date in abscissa creates a filter on the first date.

I tried also with %MDX function, but then I loose the context for the CURRENTMEMBER.

Discussion (2)0

Add reply

Comments

Alexander Koblov · Jun 8, 2016

Hi Blaise.

Does following query looks like what you need?

With Member Measures.[RunningTotal] As 
     'AGGREGATE(%TIMERANGE(DateOfSale.[Actual].[MonthSold].&[201101],DateOfSale.CurrentMember),
                MEASURES.[Amount Sold])',FORMAT_STRING='$#,#.#' 
SELECT {Measures.[Amount Sold],Measures.[RunningTotal]} ON 0,  
        [DateOfSale].[Actual].YearSold.&[2016].CHILDREN ON 1 
FROM HOLEFOODS

It prints two columns -- Revenue in current month and Total revenue from 2011-01 up to current month. Rows are months of 2016 year.

0 0

Evgeny Shvarov Jun 8, 2016 to Alexander Koblov

Very nice Alexander!

Will it work only for time dimensions with the help of %Timerange or I can use the similar approach for any type of dimensions?

0 0