Number of customers

[pstanek]

Hi I have table with these columns Shop , customer card, data of shopping , revenue, I have some idea how to calculate date of the cfist purchase, but I want to calculate  for some date , number of customers who  who made the first purchace that day  . Do you have any idea.  how to do it?

[v-ljerr-msft]

Hi @pstanek,

I am wondering if the formula(untested) below works in your scenario? 

measure =
VAR firstCustomer =
    FIRSTNONBLANK ( 'Table1'[Customer], 1 )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER ( 'Table1', 'Table1'[Customer] = firstCustomer )
    )

Regards

[pstanek]

I  think , it will find only find first customer, not first purchases.

Column customer is blank, or there is the customer card number.

If date is  the oldest for certain customer card number, it means that date the first purchase was  made the  by that customer.

For certain date I want to count customer card  numbers( unique) that hasn´t appear before that date.

It means  They made the first purchases that date .

[pstanek]

 I was right, function summarize is not available. I will have to find another solution.

[pstanek]

Hi I create example.

Result of measure should be 2 for 29.05.2017, because  there we have  customer card 2 nad 7  made the first purchases. For 28.05.2017 result should be 1 , because only customer card 4 made the first purchase.

I need solution that be available for direct query.

[v-ljerr-msft]

Hi @pstanek,

Based on my test, the formula below should work in your scenario. 

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[Custom Card] ),
    FILTER (
        Table1,
        Table1[Custom Card] <> BLANK ()
            && NOT (
                CONTAINS (
                    FILTER ( ALL ( Table1 ), Table1[Shopping Date] < MAX ( Table1[Shopping Date] ) ),
                    Table1[Custom Card], Table1[Custom Card]
                )
            )
    )
)

Regards

[pstanek]

only one error in measure ,CONTAINS is not supported by DirectQuery.

[v-ljerr-msft]

Hi @pstanek,

Based on my test, the CONTAINS function should be supported in DirectQuery mode.

Have you enabled the 'Allow unrestricted measures in DirectQuery mode' option under File > Options > DirectQuery? 

Regards

[Greg_Deckler]

You could create a measure that did a SUMMARIZE of your table filtered by the date that grouped by customer and had an expression that was a COUNT?

[pstanek]

I am not sure it can work within Direct Query. I thought functions (with result in form of table) are not supported in that mode