Count Customers with at least one transaction every month

[AREO-07]

Hi Community,

I am fairly new to PowerBI and DAX measures. Im in need of assistance with a problem encountered.

The problem is simple but I do not know how to write it in a DAX measure.

I want to count the number of customers with atleast one transaction every month.

My table has CustID, Transaction Date and Amount.

Sample Table:

CustID     Trans Date     Amount

ABC123   1/1/20            100

EFG456    1/10/20          90

DEF789     1/15/20          80

ABC123     2/15/20          100

DEF789      2/20/20          70

ABC123    3/10/20           100

EFG456     3/30/20           50

From the sample table 1 out of 3 customer has transactions atleast once a month so the count is 1.

I want it to be DAX so that if a new data come in for the current month and one CustID from the previous count

did not have any purchase for the current month it will update the count and exclude that CustID.

I hope someone can help me with my problem.

Thanks

[amitchandak]

@AREO-07 , You need to have date table with Month Year join to your date in the table  

Try like

countx(filter(summarize(Date,Table[CustID],"_1",calculate(distinctCOUNT(Date[Month year]), allselected(Date)), "_2",distinctCOUNT(Date[Month year])),[_1]=[_2]),[CustID])

[AREO-07]

I have a separate calendar table as my date table

I have tried the DAX but the result was the same with the distinctcount of CustID.

Loyal User = COUNTX(FILTER(SUMMARIZE(Append1,Append1[Loyalty Card Number],"Unique User",

CALCULATE(DISTINCTCOUNT('Calendar'[MonthYear]),ALLSELECTED('Calendar'[Date])),"Loyal User",

DISTINCTCOUNT('Calendar'[MonthYear])),[Unique User]=[Loyal User]),Append1[Loyalty Card Number])

Did i write the DAX correctly?

[amitchandak]

@AREO-07 , Seem fine try

Loyal User = COUNTX(FILTER(SUMMARIZE(Append1,Append1[Loyalty Card Number],"Unique User",

CALCULATE(DISTINCTCOUNT('Calendar'[MonthYear]),ALLSELECTED('Calendar'[Date])),"Loyal User",

CALCULATE(DISTINCTCOUNT('Calendar'[MonthYear]),not(isblank(Append1[Loyalty Card Number])))),[Unique User]=[Loyal User]),[Loyalty Card Number])

Forced the join in two tables by checking not blank, in the second measure

[AREO-07]

@amitchandak 

Thank you. I tried the updated DAX but the result did not changed.

[Anonymous · Accepted solution]

// Assumptions:
// Customers - dimension that stores customers, key: CustID
// Dates     - dimension storing days, key Date
// Sales     - fact with at least CustID, TranDate, Amount
// 
// Customers 1 <-1way- * Sales on CustID
// Dates 1 <-1way- * Sales on [Date] = [TranDate]
//
// Dates must contain a unique identifier of a month,
// something like 202001, which is Jan 2020. Call it
// YearMonthID.

// The mechanics of the measure:
// From the set of all customers visible
// in the current context, count those that
// have at least one transaction in each
// of the months that are visible in the
// visual (requires the use of ALLSELECTED).
// If not full months are selected, for instance,
// only weekends are visible, then consider
// only the weekends in the relevant months.

[# Cust With Tx Each Month] =
var __countOfVisibleMonths =
	CALCULATE(
		DISTINCTCOUNT( Dates[YearMonthID] ),
		ALLSELECTED( Dates )
	)
var __countOfCustsWithTxEachMonth =
	SUMX(
		VALUES( Customers[CustID] ),
		// there must be at least 1 txn
		// for each of the __visibleMonths
		var __hasAtLeast1TxnEachMonth = 
			CALCULATE(
				COUNTROWS(
					SUMMARIZE(
						Sales,
						Dates[YearMonthID]
					)
				) = __countOfVisibleMonths,
				ALLSELECTED( Dates )
			)
		return
			if( __hasAtLeast1TxnEachMonth, 1 )
	)
return
	__countOfCustsWithTxEachMonth