Cumulative Distinct Count by Month

Question

Hello,&nbsp;&nbsp;This is driving me crazy and is probably something simple that I'm overlooking..&nbsp;Trying to create a calculated column that counts the distinct # of employees to date.&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Curent Count&nbsp; &nbsp; &nbsp; &nbsp; Cumulative CountJan - 2 Employees&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2Feb - 2 Employees&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4Mar - 2 Employees&nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp;For some reason the cumulative numbers aren't working out.&nbsp;&nbsp;Calculated Formula:&nbsp;&nbsp;Cumulative =&nbsp;CALCULATE (DISTINCTCOUNT( [Employee] ),FILTER (ALL('YTD Attrition'),[Term Date] &lt;= MAX ( [Term Date] )))&nbsp;Here's what it looks like:&nbsp;&nbsp;Thanks in advance!

zubair_muhammad · Answer

Hi PowerBIzNess&nbsp;Try replacing "MAX" with "EARLIER"

powerbizness · Answer

Zubair_Muhammad&nbsp;Thanks for the reply. That made the number's a little less crazy but something is still throwing it off..&nbsp;

zubair_muhammad · Answer

Hi PowerBIzNess&nbsp;Please could you share your file via googledrive or onedrive or copy paste dataset

anonymous · Answer

PowerBIzNess,You can use the following DAX to calculate cumulative. Make sure that&nbsp; you choose "Don't summarize" for cumulative column in table visual.
Cumulative = CALCULATE(DISTINCTCOUNT('YTD Attrition'[Employee]),ALL('YTD Attrition'),'YTD Attrition'[Term Date]&lt;=EARLIER('YTD Attrition'[Term Date]))
Regards,Lydia

powerbizness · Answer

Anonymous&nbsp;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:&nbsp;John Doe&nbsp; &nbsp; 5/1/2017&nbsp; &nbsp;CompensationJohn Doe&nbsp; &nbsp; 5/1/2017&nbsp; &nbsp;RelocationJohn Doe&nbsp; &nbsp; 5/1/2017&nbsp; &nbsp;Benefits&nbsp;Any suggestions you have on improving this would be appreciated!&nbsp;

Forum Discussion

Cumulative Distinct Count by Month

10 Replies

Related content

count distinct

Cumulative Count DAX

Cumulative distinct count

Cumulative distinct count

Distinct Count