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
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

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