Forum Discussion
arif_ali
Helper I
Helper ISub total in filter
Hello All, I have a count of units for a selected period. I am trying to count only those who have bought an average of 2 units i.e. total units bought 9 in 3 months so an average of 3. I want ...
// Dealers must be a dimension connected // to the fact table on DealerId. Active Dealers = VAR mStartDate = MIN( 'Business Dates'[Date] ) VAR mEndDate = MAX( 'Business Dates'[Date] ) VAR mMonths = DATEDIFF( mStartDate, mEndDate, MONTH) + 1 var __dealers = SUMX( Dealers, 1 * ( [Products CY] >= 2 * mMonths ) ) RETURN __dealersBest
D
arif_aliHelper I
Helper IHi D,
Here is the code
Active Customers =
VAR mStartDate = MIN('Business Dates'[Date])
VAR mEndDate = LASTDATE('Business Dates'[Date])
VAR mMonths = DATEDIFF(mStartDate,mEndDate,MONTH)+1
VAR mProducts = IF(ISBLANK([Products CY]),0,[Products CY])
VAR mAverage = DIVIDE(mProducts,mMonths)
RETURN
SUMX('Calc Active Dealers',1*(mAverage>=2))
The results is 689 whereas I only a total of 350 customers. It is counting each customer for each month as opposed to one time for the period selected).
Thank you,
// Dealers must be a dimension connected
// to the fact table on DealerId.
Active Dealers =
VAR mStartDate = MIN( 'Business Dates'[Date] )
VAR mEndDate = MAX( 'Business Dates'[Date] )
VAR mMonths =
DATEDIFF( mStartDate, mEndDate, MONTH) + 1
var __dealers =
SUMX(
Dealers,
1 * ( [Products CY] >= 2 * mMonths )
)
RETURN
__dealers
Best
D