Forum Discussion
SachinNamdeo-20
Helper II
Helper IIActive customers count
"Dear community, I have 5 years of data at the invoice level. I am trying to calculate the no. of customers who purchased at least 3 times in the current FY and sales quantity of that customers. FY ...
If you have a date table linked to the fact table you can try
Active Customers = VAR SummaryTable = ADDCOLUMNS ( SUMMARIZE ( Sales, Sales[Customer] ), "@num months", COUNTROWS ( CALCULATETABLE ( SUMMARIZE ( Sales, 'Date'[Year month] ) ) ) ) RETURN COUNTROWS ( FILTER ( SummaryTable, [@num months] >= 3 ) )- calculation done
Active dealer in ficial year = CALCULATE([Active Customers],DATESYTD(Calendar_new[Date],"03/31"))
tamerj1
Community Champion
Community ChampionHi SachinNamdeo-20
Please refer to amended sample file with the solution
Active Customers =
COUNTROWS (
FILTER (
GROUPBY (
SUMMARIZE (
CUBE_INVOICE,
CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE],
Calendar_new[YM]
),
CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE],
"@Months",
SUMX ( CURRENTGROUP ( ), 1 )
),
[@Months] >= 3
)
)
SachinNamdeo-20Helper II
Helper IIThank you sir for your valuable suggestion