Distinct Count of non-zero records

[onape]

Hello, I'm hoping I could get some help with something I'm working on. I have a dataset that has values of customers investment per day. some days may be missing from the dataset. My visual has a date slicer (mm-yyyy). I need to do a distinct count of customer ID by Type in my table visual such that only last non-zero value amount on or before the selected date are counted.

The right hand side shows records that will be counted on not based on the filter

so for example, if April 2024 is selected in my filter , the result will be

I have tried a numberof DAX but none is working correctly . Most of them do not count a record if the value date is not in the month of the date filter.

Any help with this will be highly appreciated. 

Thank you

[onape]

Thank you @johnt75 .I think that the code still need a bit of modification please. For example, in January the result should be

because although there are 3 counts for Blossom by the end of Jan, 2 of them are for the same customer ID (A) so that's 1 count and the one for customer C is the 2nd count. Please see extract below

For Aug for example, it should look like

based on

[johnt75]

That doesn't match the data. Blossom in January has 3 unique customers

These 3 would obviously also count in August, as we are looking at all values before the specified date.

[onape]

Apologies if I didn't explain well initially. Using the screenshot you pasted, 

Considering January, the latest values for account numbers 111, 113 & 130 were non-zero  by the end of the month of january. however, 118 went from £15 to £0 so it is meant to be excluded from the count. Based on that, we'll just the counting A & C.

The below shows the latest values of the accounts by the end of August

For unique count of non-zero values by Aug ending, Blossom = 2 (CustomerIDs A & C), Archi = 1 (CustomerID B), Rowa = 1 (CustomerID A)

I hope it's clearer. 

Thanks.

[johnt75]

Ah, that needs a different approach

Num Customers = 
Var ReferenceDate = MAX('Calendar Table'[Date])

Var BaseTable = 
ADDCOLUMNS(
	CALCULATETABLE( 
		SUMMARIZE( 
			Sheet1, 
			Sheet1[Account Number],
			Sheet1[Customer ID]
		), 
		'Calendar Table'[Date] <= ReferenceDate 
	),
	"@Amount",
	VAR MaxDate = CALCULATE( MAX( Sheet1[Value Date] ), 'Calendar Table'[Date] <= ReferenceDate )
	VAR Amount = CALCULATE( SUM( Sheet1[Value Amount] ), Sheet1[Value Date] = MaxDate, REMOVEFILTERS( 'Calendar Table' ) )
	RETURN Amount
)

VAR SummaryTable = FILTER( 
	BaseTable,
	[@Amount] > 0
)
	
VAR Result = 
    // COUNTROWS(SummaryTable)
	//COUNTROWS( BaseTable )
	COUNTROWS(
		GROUPBY( SummaryTable, Sheet1[Customer ID] )
	)
	

RETURN
    Result

	

[onape]

Thank you so much. This is amazing! Is it possible to always have the correct total or I just have to live with that?😊

[johnt75 · Accepted solution]

I think this should do it

Num Customers = 
Var ReferenceDate = MAX('Calendar Table'[Date])
VAR TypesToUse = CALCULATETABLE(
	ALLSELECTED( Sheet1[Type] ),
	REMOVEFILTERS( 'Calendar Table' )
)
VAR Result = SUMX( KEEPFILTERS( TypesToUse ),
    Var BaseTable = 
    ADDCOLUMNS(
        CALCULATETABLE( 
            SUMMARIZE( 
                Sheet1, 
                Sheet1[Account Number],
                Sheet1[Customer ID]
            ), 
            'Calendar Table'[Date] <= ReferenceDate 
        ),
        "@Amount",
        VAR MaxDate = CALCULATE( MAX( Sheet1[Value Date] ), 'Calendar Table'[Date] <= ReferenceDate )
        VAR Amount = CALCULATE( SUM( Sheet1[Value Amount] ), Sheet1[Value Date] = MaxDate, REMOVEFILTERS( 'Calendar Table' ) )
        RETURN Amount
    )

    VAR SummaryTable = FILTER( 
        BaseTable,
        [@Amount] > 0
    )
        
    VAR Result = 
        COUNTROWS(
            GROUPBY( SummaryTable, Sheet1[Customer ID] )
        )
        

    RETURN
        Result
)
RETURN Result

[onape]

Thank you very much, @johnt75 . This works perfectly.