Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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
@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.
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
@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..
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
@v-ljerr-msft thanks for the reply
I have sent you the datasheets and sample pbix file
Please check once.
Can anyone please suggest me about this....
User | Count |
---|---|
89 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |