Reply
Highlighted
Frequent Visitor
Posts: 4
Registered: ‎12-13-2016
Accepted Solution

DAX Help Needed - Trailing 12 Month Total

Retention.GIF

 

 Hi everyone,

 

I am new to PowerBI and can really use your help with DAX funcitons. As you can see from the mockup table above, I need to create a measure called retention rate. The way it works is to sum up the past 12 month cancels, say we are looking at November 2016 retention rate, it is calculated by deviding the sum of cancels from 2016-11 to 2015-12, by the beginning balance as of December 2015, and plus 100%. In excel I can easily achieve that through SUMIFS. Will you please help?

 

Thank you in advance,

PowerBI Beginner

 


Accepted Solutions
Moderator
Posts: 6,264
Registered: ‎03-06-2016

Re: DAX Help Needed - Trailing 12 Month Total

Hi @PowerBIbeginner,

 

In your scenario, you can create a Date column firstly:

 

Date = DATE('Table1'[Year],'Table1'[Month],"1")

 

Then create a column:

 

RetentionRate =
VAR last12Beg = LOOKUPVALUE(Table1[Beg Bal],Table1[Date],DATE(YEAR([Date]),MONTH([Date])-11,1) )
Return
IF(last12Beg=BLANK(),BLANK(),
DIVIDE(SUMX(FILTER('Table1','Table1'[Date]>=DATE(YEAR(EARLIER([Date])),MONTH(EARLIER([Date]))-11,1) && 'Table1'[Date]<=EARLIER([Date])),[Cancels]) +last12Beg,last12Beg))

 

q6.PNG

 

Best Regards,
Qiuyun Yu

View solution in original post

Attachment

All Replies
Super Contributor
Posts: 891
Registered: ‎06-09-2016

Re: DAX Help Needed - Trailing 12 Month Total

Dear PowerBI Beginner,


Sorry I m in a rush but visit the below link to know more about your problem.

 

http://www.daxpatterns.com/time-patterns/

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Moderator
Posts: 6,264
Registered: ‎03-06-2016

Re: DAX Help Needed - Trailing 12 Month Total

Hi @PowerBIbeginner,

 

In your scenario, you can create a Date column firstly:

 

Date = DATE('Table1'[Year],'Table1'[Month],"1")

 

Then create a column:

 

RetentionRate =
VAR last12Beg = LOOKUPVALUE(Table1[Beg Bal],Table1[Date],DATE(YEAR([Date]),MONTH([Date])-11,1) )
Return
IF(last12Beg=BLANK(),BLANK(),
DIVIDE(SUMX(FILTER('Table1','Table1'[Date]>=DATE(YEAR(EARLIER([Date])),MONTH(EARLIER([Date]))-11,1) && 'Table1'[Date]<=EARLIER([Date])),[Cancels]) +last12Beg,last12Beg))

 

q6.PNG

 

Best Regards,
Qiuyun Yu

Attachment