Forum Discussion
Helper ILast 06 month data for attrition
I am trying to get the data for last 06 month.
Example - Lets consider attrition for Jan'22 to May'22 is 1.2, 2.2, 3.3, 4.4, 5.5, 6.6 then I should get last 06 month attrition at May as - 3.8 (Average of last 06 month).
I have created a meausre for attrition percentage for each month.
Hi Chandra_maha ，
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:
Average of last 06 month = VAR _selmonth = SELECTEDVALUE ( 'Table'[Monthnum] ) VAR _num = CALCULATE ( DISTINCTCOUNT ( 'Table'[Monthnum] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Monthnum] <= _selmonth && 'Table'[Monthnum] >= _selmonth - 5 ) ) VAR _sumofattr = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Monthnum] <= _selmonth && 'Table'[Monthnum] >= _selmonth - 5 ), [Attrition Rate] ) RETURN DIVIDE ( _sumofattr, _num )If the above one can't help you get the desired result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
3 Replies
Hi Chandra_maha ,
You can refer the following links to get it:
Computing rolling average in DAX
Average Last 6 months accumulated
Average sales last 6 months for completed months
If the above one can't help you get the desired result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Chandra_mahaHello Anonymous
Thanks for reply. I treid above code. However its not working as expected. Here is the raw data and some graphis as expected result.
I have calculated the attrtion (Red Color) and its in measure. Now, I need to calculate "Average Rate of 6 month" (Blue one).
Suppose -Attrition for Jan'22 to Dec'22
Month Attrition Rate Jan'22 1.2 Feb'22 2.2 March 3.3 Apr 4.4 May 5.5 June 6.6 July 7.7 Aug 8.8 Sep 9.9 Oct 9.1 Nov 5.4 Dec 6.5 By above data, I can plot "Attrition" graph (Red Graph). However, I am unable to plot Average Rate of 6 month" (Blue one).
Red Graph provide the montly Attrition rate.
Blue Graph should provide the average of last 6 month. Suppose I click on May(Blue Graph) then May'22 is 1.2, 2.2, 3.3, 4.4, 5.5, 6.6 then I should get last 06 month attrition at May as - 3.8 (Average of last 06 month)
Its bit confusing. If you have any concern regarding data, ping me 🙂Hi Chandra_maha ，
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:
Average of last 06 month = VAR _selmonth = SELECTEDVALUE ( 'Table'[Monthnum] ) VAR _num = CALCULATE ( DISTINCTCOUNT ( 'Table'[Monthnum] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Monthnum] <= _selmonth && 'Table'[Monthnum] >= _selmonth - 5 ) ) VAR _sumofattr = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Monthnum] <= _selmonth && 'Table'[Monthnum] >= _selmonth - 5 ), [Attrition Rate] ) RETURN DIVIDE ( _sumofattr, _num )If the above one can't help you get the desired result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards