Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

  • Anonymous , Try measure  like

    sumx(values(Table[N Cate]),[Win Back 1])

    or

    sumx(Summarize(Table,Table[N Cate], "_1",[Win Back 1]),[_1])

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi amitchandak

       

      I tried your solutions but no results, it returns 0.

       

      Mohammad PATEL