This is driving me crazy and is probably something simple that I'm overlooking..
Trying to create a calculated column that counts the distinct # of employees to date.
Curent Count Cumulative Count
Jan - 2 Employees 2
Feb - 2 Employees 4
Mar - 2 Employees 6
For some reason the cumulative numbers aren't working out.
DISTINCTCOUNT( [Employee] ),
[Term Date] <= MAX ( [Term Date] )
Here's what it looks like:
Thanks in advance!
You can use the following DAX to calculate cumulative. Make sure that you choose "Don't summarize" for cumulative column in table visual.
Cumulative = CALCULATE(DISTINCTCOUNT('YTD Attrition'[Employee]),ALL('YTD Attrition'),'YTD Attrition'[Term Date]<=EARLIER('YTD Attrition'[Term Date]))
thanks so much for the reply. I realized my issue was the summarization in the visual, which displayed correctly when I selected 'Average'. For some reason the 'Don't Summarize' option doesn't appear as an option - I think this is because in order to gather some additional metrics I unpivoted a list of columns detailing attrition reasons, which created multiple rows for the same individuals. So for example my data looks like:
John Doe 5/1/2017 Compensation
John Doe 5/1/2017 Relocation
John Doe 5/1/2017 Benefits
Any suggestions you have on improving this would be appreciated!
The DAX also works in your scenario, please check the following screenshot.