Cumulative Distinct Count by Month

[PowerBIzNess]

Hello, 

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. 

Calculated Formula: 

Cumulative = 
CALCULATE (
DISTINCTCOUNT( [Employee] ),
FILTER (
ALL('YTD Attrition'),
[Term Date] <= MAX ( [Term Date] )
)
)

Here's what it looks like:

Thanks in advance!

[v-yuezhe-msft]

@PowerBIzNess,

You can download my PBIX file to test.

Regards,
Lydia

[v-yuezhe-msft]

@PowerBIzNess,

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]))

Regards,
Lydia

[PowerBIzNess]

@v-yuezhe-msft

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!

[v-yuezhe-msft]

@PowerBIzNess，

The DAX also works in your scenario, please check the following screenshot.


Regards,
Lydia

[PowerBIzNess]

Thanks Lydia

What is your default summarization for the column? I don't have a 'Don't Summarize' option so I'm left having to average the numbers.

[Zubair_Muhammad]

Hi @PowerBIzNess

Try replacing "MAX" with "EARLIER"

[PowerBIzNess]

@Zubair_Muhammad

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

[Zubair_Muhammad]

Hi @PowerBIzNess

Please could you share your file via googledrive or onedrive or copy paste dataset