cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBIzNess Frequent Visitor
Frequent Visitor

Cumulative Distinct Count by Month

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:

 

Capture.PNG

 

Thanks in advance!

8 REPLIES 8
Super User
Super User

Re: Cumulative Distinct Count by Month

Hi @PowerBIzNess

 

Try replacing "MAX" with "EARLIER"

PowerBIzNess Frequent Visitor
Frequent Visitor

Re: Cumulative Distinct Count by Month

@Zubair_Muhammad

 

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

 

Capture.PNG

Super User
Super User

Re: Cumulative Distinct Count by Month

Hi @PowerBIzNess

 

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

Moderator v-yuezhe-msft
Moderator

Re: Cumulative Distinct Count by Month

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

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PowerBIzNess Frequent Visitor
Frequent Visitor

Re: Cumulative Distinct Count by Month

@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!

 

Moderator v-yuezhe-msft
Moderator

Re: Cumulative Distinct Count by Month

@PowerBIzNess

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PowerBIzNess Frequent Visitor
Frequent Visitor

Re: Cumulative Distinct Count by Month

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.

Moderator v-yuezhe-msft
Moderator

Re: Cumulative Distinct Count by Month

@PowerBIzNess,

You can download my PBIX file to test.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.