Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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))
Best Regards,
Qiuyun Yu
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))
Best Regards,
Qiuyun Yu
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |