Sum of distinct counts over filtered months

[feyd]

Hi everyone,

my dataset looks like this:

Customer ID	Amount	Amount Group	Type	Date
1	55	100	T	02/2017
2	45	50	TT	02/2017
3	50	50	TC	02/2017
1	20	50	TC	02/2017
2	50	50	T	03/2017
3	45	50	TT	03/2017
4	55	100	TC	03/2017
1	20	50	TC	03/2017
3	10	50	T	03/2017

As you can see I have 3 unique Customer IDs in 02/2017 date and 4 unique Customer ID's in 03/2017. So the sum of distinct Customer IDs for these two months is 7.

How to get this sum dynamically, based of filtering across few/or more months?

What I get as result when filtering across these two months is 4, which is true since that is the number of distinct CustomerIDs.

Please note that I need filtering capability of the result with date, type and Amount Group…

Any ideas?

Thanks!

[mattbrice]

measure =
SUMX ( VALUES ( Calendar[Month-Year] ), DISTINCTCOUNT ( Table[Customer ID] ) )

[feyd]

Thank you for quick reply!

I've created the measure and sumx returns 8 instead of 7...

[Zubair_Muhammad · Accepted solution]

Hi @feyd

Try this MEASURE

measure =
SUMX (
    VALUES ( TableName[Date] ),
    CALCULATE ( DISTINCTCOUNT ( TableName[Customer ID] ) )
)

[Zubair_Muhammad]

@feyd