DAX formula to track the number of customers having at least one purchase per period

[SelenaZ]

Dear Community,

I have the following challenge. I need to show the number of customers that have bee placing at least one order per period (month or week or whatever is the filed chosen in the Date table for the power pivot) for every period since the start of the year.  If a customer does not purchase in one period, such customer does  not get included.

I understand that there should be a filter on at least one transaction per period, but how to check that the customers have been purchasing in EVERY period since the beginning?

In the fTable there is CustomerID and date of purchase, the Date table offers various periods: months, weeks, quarters etc.

If it is not pssible to do for any period, monthly would be just fine.

Thanks in advance!

[Anonymous · Accepted solution]

@SelenaZ 

Here's the code that works out the number of customers according to your algorithm but only for months. If you want other periods, then you have to create a composite measure that will have to detect which period is the current one and then perform the relevant calculation. You'll have to use SWITCH out of necessity.

# Cust With Tx's To Date = 
// Let's do this when 1 month is visible only
var OnePeriodVisible = HASONEVALUE( Dates[Month] )
// Month must be unique across all Dates (e.g., YYYY-MM)
var CurrentMonth = DISTINCT( Dates[Month] )
var FullPeriodVisible =
    CALCULATE(
        COUNTROWS( Dates ),
        REMOVEFILTERS( Dates ),
        CurrentMonth
    ) = COUNTROWS( Dates )
var RequiredNumOfPeriods = 
    CALCULATE(
        DISTINCTCOUNT( Dates[Month] ),
        DATESYTD( Dates[Date] )
    )
var CustomersOfInterest =
    SUMMARIZE( 
        Transactions,
        Customers[CustomerID]
    )
var CustsWithTxInEachPeriodToDate =
    // We can do the calculation only if one period (month)
    // is visible in th current context and if the period
    // is fully visible, that is, all the days that constitute
    // the period are selected. If you want to have code that
    // will do the same for other periods, say, quarters or
    // weeks (be careful with weeks, though, as they do not go equally
    // into the year), you have to adjust this code to use
    // the period of choice instead of months.
    if( OnePeriodVisible && FullPeriodVisible,
        SUMX(
            CustomersOfInterest,
            INT(
                CALCULATE(
                    COUNTROWS(
                        SUMMARIZE(
                            Transactions,
                            Dates[Month]
                        )
                    ) = RequiredNumOfPeriods,
                    DATESYTD( Dates[Date] )
                )
            )
        )
    )
return
    If( CustsWithTxInEachPeriodToDate, CustsWithTxInEachPeriodToDate )

The assumptions here are the following:

1. You have Dates (dim), Transactions (fact), Customers (dim)

2. Dates[Date] 1 -one-way-> * Transactions[Tx Date]

3. Customers[CustomerID] 1 -one-way-> * Transactions[CustomerID]

The Dates table is marked as a date table in the model

Working with different periods (not only months) requires that the code detect the period and this in turn requires the use of SWITCH. The overall measure will be heavily dependent on the structure of the Dates table, which is never a good solution but you don't have a choice if you want to make your measure work with different periods. Sorry.

[SelenaZ]

Thank you so much!

[amitchandak]

@SelenaZ , Assume you have the date and customer dimensions.  And date table has month year

Sold in all selected months

measure =
countx(summarize(sales, Customer[cutomer], "_1", Distinctcount(Date[Month Year]),"_2" ,CALCULATE(Distinctcount(Date[Month Year]), filter(Sales, not(isblank(Sales[Amount])))))
, [_1] =[_2])

[SelenaZ]

Thank you! UNfortunately, I am getting an error message that COUNTX cannot work with boolean values

(I guess, referring to the condition [_1]=[_2])

Could you please help out here?

Thanks again!