Forum Discussion

Chandra_maha's avatar
Chandra_maha
Icon for Helper I rankHelper I
3 years ago
Solved

Last 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 (Avera...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

Chandra_maha's avatar
Chandra_maha
Icon for Helper I rankHelper I
3 years ago

Hello 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 

MonthAttrition Rate
Jan'221.2
Feb'222.2
March3.3
Apr4.4
May5.5
June6.6
July7.7
Aug8.8
Sep9.9
Oct9.1
Nov5.4
Dec6.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 🙂

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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