Forum Discussion
Anonymous
5 years agoNot applicable
Filtering measure based on another measure
Hello,
I'm trying to get a list of client that has not been in the store for the previous month (based on date selection) and has bought at least during the period day-40 and day-59. Here is the script:
Max Achat Date =
CALCULATE(
MAX(BITKT[TKT_DATACHAT]),
ALLEXCEPT(BITKT,BITKT[TKT_NUMCARTE])
)
Duration =
VAR tday = IF(HASONEVALUE(BIDAT[DAT_date]),FIRSTDATE(BIDAT[DAT_date]))
RETURN
IF (
[Max Achat Date] > tday,
0,
DATEDIFF ( [Max Achat Date], tday, DAY )
)
Win Back 1 = VAR day_40 = DATEADD(BIDAT[DAT_date],-40,DAY)
VAR day_59 = DATEADD(BIDAT[DAT_date],-59,DAY)
VAR quantite = CALCULATE(SUM(BITKT[TKT_QTE]),DATESBETWEEN(BIDAT[DAT_date],day_59,day_40))
RETURN
IF([Duration]>30 && quantite>1, 1,0)
What I'm trying to do now is to distinct count the number of people that are concerned by the Win Back = 1.
I tried with SUMX, COUNTAX but no results.
Could you please help me on that ?
Kind regards,
Mohammad PATEL
2 Replies
- amitchandak
Super User
Anonymous , Try measure like
sumx(values(Table[N Cate]),[Win Back 1])
or
sumx(Summarize(Table,Table[N Cate], "_1",[Win Back 1]),[_1])
- AnonymousNot applicable