cancel
Showing results for
Did you mean:
Frequent Visitor

## Formula to cumulate distinct count

Hello,

i have tried to find a solution on this forum, but im not an expert of DAX, especially when it involves dates...

I have a table with 2 columns (userID and Month):

 July UserA July UserA July UserB August UserB August UserC September UserA September UserD

I need to display per month the total of distinct users but if they exist previously they should not be count again.

I should obtain:

• July: 2
• August: 3 (2 in july + 1 new in august)
• September: 4 (3 august + 1 new in sept)

I am able to get distinct count per month, but i cant manage to filter then if they exist previously or not..

1 ACCEPTED SOLUTION
Super User

Hi @CG_pbi

First, add a new column with this code to convert Month Name to Number:

``Month No. = month(DATEVALUE("2021/"&'Table'[Month]&"/1"))``

Then use this measure to find the distinct count :

``````Measure =
VAR _A =
MAX ( 'Table'[Month No.] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[userID ] ),
FILTER ( ALL ( 'Table' ), 'Table'[Month No.] <= _A )
)``````

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

2 REPLIES 2
Super User

Hi @CG_pbi

First, add a new column with this code to convert Month Name to Number:

``Month No. = month(DATEVALUE("2021/"&'Table'[Month]&"/1"))``

Then use this measure to find the distinct count :

``````Measure =
VAR _A =
MAX ( 'Table'[Month No.] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[userID ] ),
FILTER ( ALL ( 'Table' ), 'Table'[Month No.] <= _A )
)``````

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Frequent Visitor

This works perfectly!! Thank you for your help !

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors