Formula to cumulate distinct count

[CG_pbi]

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.. 

Thank you for your help! 

[VahidDM · Accepted solution]

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.

Appreciate your Kudos!!

[CG_pbi]

This works perfectly!! Thank you for your help !