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 %|
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.
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 )
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] )
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.