cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CG_pbi
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
JulyUserA
JulyUserB
AugustUserB
AugustUserC
SeptemberUserA
SeptemberUserD

 

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! 

1 ACCEPTED SOLUTION
VahidDM
Super User
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:

VahidDM_0-1633094063915.png

 

 

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

Appreciate your Kudos!!

 

View solution in original post

2 REPLIES 2
VahidDM
Super User
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:

VahidDM_0-1633094063915.png

 

 

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
Frequent Visitor

This works perfectly!! Thank you for your help !

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

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

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

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