Need help in DAX, Distinct Count

[Anonymous]

Hi, I am trying to calculate distinct count for Supplier ID based on Member ID, but it's not working the I want, please find DAX below

DistinctSupplierID =

var __member = T[MemberID]

var __endDate = T[ReportReceived].[Date]

var __startDate = EDATE( __endDate, -6)

var __result =

CALCULATE(

    DISTINCTCOUNT(T[SupplierID]),

        SELECTCOLUMNS(

            FILTER(

                T,

                T[MemberID] = __member

// __startDate <= T[ReportReceived].[Date]

            ),

"SuppID", T[SupplierID]

    )

)

return

    __result

Data:

SupplierID	MemberID	ReportReceived	DistinctSupplierID	edate
319	1	31-03-2005 00:00	2	30-09-2004 00:00
278	1	31-03-2005 00:00	2	30-09-2004 00:00
214	1	09-08-2005 00:00	1	09-02-2005 00:00
99	1	27-01-2006 00:00	1	27-07-2005 00:00
446	1	21-02-2006 00:00	1	21-08-2005 00:00
446	1	21-02-2006 00:00	1	21-08-2005 00:00
446	1	13-09-2005 00:00	1	13-03-2005 00:00
147	1	30-04-2005 00:00	1	30-10-2004 00:00
112	2	16-12-2005 00:00	2	16-06-2005 00:00
111	2	16-12-2005 00:00	2	16-06-2005 00:00
111	2	16-01-2006 00:00	1	16-07-2005 00:00
111	2	17-02-2006 00:00	1	17-08-2005 00:00
111	2	19-10-2005 00:00	1	19-04-2005 00:00
111	2	24-08-2005 00:00	1	24-02-2005 00:00
111	2	06-07-2005 00:00	1	06-01-2005 00:00
178	2	30-07-2005 00:00	1	30-01-2005 00:00
187	2	07-01-2005 00:00	1	07-07-2004 00:00
109	2	07-11-2005 00:00	1	07-05-2005 00:00
187	2	26-07-2005 00:00	1	26-01-2005 00:00

[Greg_Deckler]

Perhaps:

Column = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('T',[MemberID] = EARLIER([MemberID])),"SupplierID",[SupplierID])))

[Anonymous]

The result is 6. I am not expecting this. I want from ant date to last 6 months how many unique Supplier Id. 

For above data the distinct supplier Id column should be 

2,2,4,3,2,2,5,3,5,5,5,3,3,3,2,3,1,4,2

@Greg_Deckler 

[Greg_Deckler · Accepted solution]

@Anonymous , OK, you want this:

Column = 
var __endDate = T[ReportReceived].[Date]
var __startDate = EDATE( __endDate, -6)
RETURN
    COUNTROWS(
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(
                    'T',
                    [MemberID] = EARLIER([MemberID]) &&
                        T[ReportReceived].[Date] <= __endDate &&
                            T[ReportReceived].[Date] >= __startDate
                ),
                "SupplierID",[SupplierID]
            )
        )
    )

[Greg_Deckler]

Oh, you commented out that line in your code so I thought you didn't want it. It is hard for me to test exactly because I'm in the US so your dates don't convert so well, let me see what I can do about that.