How to get the user lost rate ?

hi,

i can't head around problem. think i'm still thinking sql devlopper. here problem.

i have connection fact table contains attribute member id , date attribute. need know how many user have lost on period of time (churn rate). rate should between first 30 day period (p1) , next 30 days until (p2). theoretically :

((count distinct member in p1 - count distinct member both in p1 , p2) * 100) / (count distinct member in p1)

but can't find measures and/or calculated members job. part can compute (count distinct member in p2), <hich monthly active use member, using aggregate lastperiods

  aggregate(lastperiods(30,[calendar].[pk date].currentmember), [measures].[daily active user])  

[measures].[daily active user] count distinct on member id.

not me @ all. tried find way (count distinct member in p1) part, ssas doesn't seem accept
lastperiods parallelperiod second argument.

as might see i'm lost. i'm learning alone. i'll buy book tomorrow if able unlock me on 1 i'll grateful.

 

 

hi paul,

thank answer. answer question if wanted result per month. i'm trying each day computation last 30 day, rolling period i've seen called. i'll use data build chart day granularity.

still helped me understand little more on tool , found resource can me : http://www.slideshare.net/markginnebaugh/mdx-what-bi-developers-need-to-know (slide 43)

 

thanks

SQL Server  >  SQL Server Analysis Services