Distinct Count and then Total Customers based on Month Selected

[Chanleakna123]

hi all , 

i have below data in daily basis recorded by each transaction in 1st table.

and 2nd table is the result i want. 

Currently i use the formula of distinct count which means when i filter 3 months together we lost some customers due to the same customer code is not counted. but what we want is , Total Distinct Customers by Month and then when the user filter 3 months together , it will count total distinct customers by each month together.

i have master table of calendar and Master Data as relationship. 

[PaulDBrown · Accepted solution]

@Chanleakna123 

1) Create the distinct count measure:

Customer Distinct count =
DISTINCTCOUNT ( Table[Customer Code] )

2) Use this final measure:

Final count of Customers =
SUMX (
    SUMMARIZE (
        'Calendar Table',
        'Calendar Table'[Month],
        "@bymonth", [Customer Distinct count]
    ),
    [@bymonth]
)

[Jihwan_Kim · Accepted solution]

Hi, @Chanleakna123 

Please check  the below picture and the sample pbix file's link down below.

Distinct Count Customer =

DISTINCTCOUNT(Sales[Customer Code])

Distinct Count Customer Total Fix =

SUMX(VALUES(Dates[Month Name]), [Distinct Count Customer])

https://www.dropbox.com/s/vgqjkvlm1pyfa0l/chanleakna.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[PaulDBrown · Accepted solution]

@Chanleakna123 

1) Create the distinct count measure:

Customer Distinct count =
DISTINCTCOUNT ( Table[Customer Code] )

2) Use this final measure:

Final count of Customers =
SUMX (
    SUMMARIZE (
        'Calendar Table',
        'Calendar Table'[Month],
        "@bymonth", [Customer Distinct count]
    ),
    [@bymonth]
)