Calculate Moving average of Attrition

[Anonymous]

Hello All,

I am calculating moving average of attrition data which i have.

I have three tables.

1st Table:-- HeadCount Table which is having active people.

I have calculated the count as per my organization rule using below formula.

_Active HeadCount = CALCULATE (
    COUNT(HeadCount[Personnel Number]),
    FILTER (
        ALLEXCEPT(HeadCount,HeadCount[Gender],HeadCount[DU Description],HeadCount[Grade],HeadCount[SBU],HeadCount[Yearly Normalized Rating]),
		
        HeadCount[Date] <= SELECTEDVALUE ( ( HeadCount[Date]) )
    )
)

Here I have mentioned in Filter the column names which it should get filte in AllEXCEPT funtion, 

With this i am able to get Active headcount values as per the filteration.

2nd Table:- Release Data

In this table i have data regarding the people who released.

And i have calculated the count of release people using the below dax which is similar to above.

_Release Count = CALCULATE (
    COUNT('Release Data'[Personnel Number]),
    FILTER (
        ALLEXCEPT('Release Data','Release Data'[Attrition],'Release Data'[DU Description],'Release Data'[Gender],'Release Data'[Reason for release biz HR],'Release Data'[SBU],'Release Data'[Tenure Category],'Release Data'[Yearly Normalized Rating FY17]),
		
        'Release Data'[Date] <= SELECTEDVALUE ( ( 'Release Data'[Date])
    )
))

3rd Table:- Month Year Table

In my month year table i have column Date(MM/DD/YYYY format) which is having relation with Date column of above two tables.

Now the actual formula for moving average of attrition is

_Release Count/_Active_HeadCount

with this i got the average of attrition.

But when i included Gender Column from HeadCount table i got the output which is wrong as 

And when i included gender column from release data table im getting below values which are wrong.

And then i have created a bridge table for Gender and created relation with both above tables of gender column.

Then when i included gender column from Gender table i got the output which is also wrong 

Any help to solve this.

[v-ljerr-msft]

Hi @Anonymous,

Based on my test with your shared pbix file, the formulas below should work in your scenario.

_Release Count = CALCULATE (
    COUNT('Release Count'[Personnel Number]),
    FILTER (
        ALL('Release Count'[Date]),
        'Release Count'[Date] <= SELECTEDVALUE ( ('Release Count'[Date]) )
    )
) 

_Sum of prv Headcount = CALCULATE (
    COUNT(HeadCount[Personnel Number]),
    FILTER (
        ALL(HeadCount[Date]),
        HeadCount[Date] <= SELECTEDVALUE ( ( HeadCount[Date]) )
    )
)

Regards

[Anonymous]

@v-ljerr-msft Can you please give any suggestions...

[v-ljerr-msft]

Hi @Anonymous,

Could you try the formula below to see if it works? 

_Release Count = 
CALCULATE (
    COUNT('Release Count'[Personnel Number]),
    FILTER (
        ALL('Month Year'[Date]),
        'Month Year'[Date] <= MAX('Month Year'[Date] )
    )
) 

Regards

[Anonymous]

@v-ljerr-msft thanks for the reply...

ya it is giving me the output as summing up the previous month values.

But if you observe the below image,

Its repeating the last value where actually theres no values exist from Dec-2017.

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

In this scenario, the formula below should work. 

_Release Count =
CALCULATE (
    COUNT ( 'Release Count'[Personnel Number] ),
    FILTER (
        ALL ( 'Month Year'[Date] ),
        'Month Year'[Date] <= MAX ( 'Month Year'[Date] )
            && 'Month Year'[Date] <= MAX ( 'Release Count'[Date] )
    )
)

Regards

[Anonymous]

@v-ljerr-msft Thanks for the  help buddy...

But i dont see that, it is giving a moving average.

if you observe the below image

Here the Jan-17 Male value is not getting summedup with Feb-17 Male value.

I tried to use the ALLEXCEPT funtion as below

But it is giving the error as

A single value for column 'Date' in table 'Release Data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Can you please help me with this..

[v-ljerr-msft]

Hi @Anonymous,

Could you share a sample pbix file(with just some sample/mock data) which can reproduce the issue, so that we can better assist on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Anonymous]

@v-ljerr-msft thanks for the reply 

I have sent you the datasheets and sample pbix file 

Please check once.

[Anonymous]

Can anyone please suggest me about this....