Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerBIbeginner
Regular Visitor

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

 

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BhaveshPatel
Community Champion
Community Champion

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.