Daily Sales Outstanding and Reverse Running SUM in MDX

hi

i'm relatively new mdx after many years doing sql. i've run business challenge in our cube relating called daily sales outstanding (dso). challenge when given debt amount in given point in time, have count each day, summing billed amounts per day until reach debt amount started with. i.e should tell many days of billing takes reach our debt amount. see example below:

current debt = 5000

day [n]           billed amount  cumulative amount

day 1            1200                 1200            (although i've started @ day 1, in date terms start @ recent day                                                             , count backwards in time)

day 2              1000               2200

day 3              1500              3700

day 4             1000               4700

day 5             800                5500 <--- debt amount reached on day 5, hence figure i'm after in example "5"

day 6

day 7

i've googled around , there couple of similar examples around none i've managed work successfully.

if appreciated.

thanks

mike

 

 

hi mike,

could please explain in more details "most recent month"? reset value when doing cumulative calcuation, add logic program in cumulative formula.

member cumcountofdays
as
sum( null : [date].[year - month - week - date].currentmember,count(nonempty   
(descendants([date].[year - month - week - date], [date].[year - month - week - date].[date]),iif(<"most recent month">,<reset measure value> ([measures].[value],[kpi].[kpi].&[12]))))

thanks,
jerry

SQL Server  >  SQL Server Analysis Services