how to get privous 3 month average from a measure

To answer all your technical questions in work.
stu1190
Posts: 11
Joined: Sun Sep 27, 2015 11:04 pm

how to get privous 3 month average from a measure

Postby stu1190 » Wed Mar 09, 2016 11:47 am

I have a cube with dimDate (Date, Month) , dimMarket (Market ) and factMonthlyUsers(Date, Market, UserCounts, ReturnedUserCounts). The Date column is first day of each month in factUser. I have a measure MonthlyRetentionoRate = Sum(ReturnedUserCounts)/Sum(UserCounts). Now I need to create a measure of average retention rate of previous 3 months (not include current month).

The dimDate table is as below

Date Month
2016-01-01 January 2016
2016-01-30 January 2016
2016-01-31 January 2016
2016-02-01 February 2016

Date Market UserCounts ReturningUsers
1/31/2016 0:00 de-DE 15097 333
1/31/2016 0:00 en-AU 2571 788
1/31/2016 0:00 en-CA 1816 33
1/31/2016 0:00 en-GB 17307 0


Any recommendation on how to solve it? thanks!

stu1190
Posts: 11
Joined: Sun Sep 27, 2015 11:04 pm

Re: how to get privous 3 month average from a measure

Postby stu1190 » Thu Mar 10, 2016 11:32 am

This has been solved. the formula I used is as below:

First create a measure with 3 month sum , then divid it by the months accumulated

3MonthMovingSumRetentionRate:=IF(COUNTROWS(VALUES('Date'[Month]))=1,
CALCULATE(SUMX(
DATESINPERIOD('Date'[Month], EOMONTH(VALUES('Date'[Month]),-1), -3, MONTH),
[MoM Retention Rate]
)))

Pre3MonthAvgRetentRate2:=IF(COUNTROWS(VALUES('Date'[Month]))=1,
DIVIDE([3MonthMovingSumRetentionRate], (CALCULATE(DISTINCTCOUNT('Date'[Month]),
DATESINPERIOD('Date'[Month], EOMONTH(VALUES('Date'[Month]),-1), -3, MONTH)
) - 1 )))

ZEROONE
Site Admin
Posts: 157
Joined: Thu Apr 09, 2015 7:14 pm

Re: how to get privous 3 month average from a measure

Postby ZEROONE » Thu Mar 10, 2016 2:35 pm

Thanks for sharing to us.


Return to “Technical Questions 技术问题”

Who is online

Users browsing this forum: No registered users and 0 guests