Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I am looking for a formula that will sum up the last 12 months of the Natural attrition % measure that I have created
Natural Attrition measure Formula =
Year | Month | Total Head Count | Sum of hc_loss | Sum of hc_gain | Natural Attrition % |
2018 | March | 10000 | 200 | 100 | 2.00% |
2018 | April | 10010 | 201 | 101 | 2.01% |
2018 | May | 10011 | 202 | 102 | 2.02% |
2018 | June | 10012 | 203 | 103 | 2.03% |
2018 | July | 10013 | 204 | 104 | 2.04% |
2018 | August | 10014 | 205 | 105 | 2.05% |
2018 | September | 10015 | 206 | 106 | 2.06% |
2018 | October | 10016 | 207 | 107 | 2.07% |
2018 | November | 10017 | 208 | 108 | 2.08% |
2018 | December | 10018 | 209 | 109 | 2.09% |
2019 | January | 10019 | 210 | 110 | 2.10% |
2019 | February | 10020 | 211 | 111 | 2.11% |
2019 | March | 10021 | 212 | 112 | 2.12% |
2019 | April | 10022 | 213 | 113 | 2.13% |
2019 | May | 10023 | 214 | 114 | 2.14% |
2019 | June | 10024 | 215 | 115 | 2.14% |
2019 | July | 10025 | 216 | 116 | 2.15% |
2019 | August | 10026 | 217 | 117 | 2.16% |
2019 | September | 10027 | 218 | 118 | 2.17% |
2019 | October | 10028 | 219 | 119 | 2.18% |
2019 | November | 10029 | 220 | 120 | 2.19% |
2019 | December | 10030 | 221 | 121 | 2.20% |
Hi Everyone,
Thank you for your help, I finally managed to solve the problem - Here is the DAX measure that worked:
You can use the WINDOW function,
Natural Attrition rolling 12 months =
SUMX (
WINDOW (
-11,
REL,
0,
REL,
ALL ( 'Date'[Year month] ),
ORDERBY ( 'Date'[Year month], ASC )
),
[Natural Attrition]
)
you may need to tweak the ORDERBY if your Year Month column should be sorted by a different column, e.g. Year Month Number.
Hi,
Thank you for your answer, I would love to be able to use this function, but my organisation only has the update from September 2022 and this came out in December 2022. Would you know of a workaround that I can do until the organisation updates our software?
I think this should work
Natural attrition rolling 12 months =
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR MinDate =
EOMONTH ( MaxDate, -12 ) + 1
VAR SummaryTable =
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( 'Date'[Year month] ),
DATESBETWEEN ( 'Date'[Date], MinDate, MaxDate )
),
"@value", [Natural Attrition]
)
RETURN
SUMX ( SummaryTable, @value )
Hi
Thank you for your response, however this also did not work but I did receive higher percentage results to what I have been getting previously. Still not the correct results though.
I have come to the conclusion that I need to create either a calculated column that includes the Natural Attrition % (that I can't seem to get working in Power BI) Or bring it into the data using SQL.
This is my calculated Column formula =
I think I have it working with the below 2 measures
Attrition Measure = SUMX(
VALUES( 'Date'[Year Month] ),
CALCULATE( DIVIDE( SUM( 'Table'[Sum of hc_loss] ), SUM( 'Table'[Total Head Count] ) ) )
)
Natural Attrition rolling 12 months =
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR MinDate =
EOMONTH ( MaxDate, -12 ) + 1
VAR SummaryTable =
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( 'Date'[Year month] ),
DATESBETWEEN ( 'Date'[Date], MinDate, MaxDate )
),
"@value", [Attrition Measure]
)
RETURN
SUMX ( SummaryTable, [@value] )
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
206 | |
90 | |
61 | |
59 | |
57 |