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

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.