Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help on calculating a measure when selecting a specific date

Hello,

 

I am trying to calculate a measure that gives me the number of client that have bought from day -59 to -40 (depending on selection date) and have not bought during the last 30 days.

 

I searched on the forum and found something that inspired me and I adapted it in order to fit the required conditions

:

 

 

test = 

var tday = IF(HASONEVALUE(BIDAT[DAT_mois_nom]),FIRSTDATE(BIDAT[DAT_date]))
VAR day_30 = FIRSTDATE(DATEADD(BIDAT[DAT_date],-30,DAY))
VAR day_40 = FIRSTDATE(DATEADD(BIDAT[DAT_date],-40,DAY))
VAR day_59 = FIRSTDATE(DATEADD((BIDAT[DAT_date]),-59,DAY))

RETURN
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( BITKT[TKT_NUMCARTE] ),
            "Purchase_Between_40_59", CALCULATE (
                COUNTROWS ( BITKT ),DATESBETWEEN(BIDAT[DAT_date],day_59,day_40),ALLEXCEPT( BICLID,BICLID[CLID_mobile],BICLID[CLID_NOM],BICLID[Alea])
            ),
            "Last30DaysPurchase", CALCULATE (
                COUNTROWS ( BITKT ),DATESBETWEEN(BIDAT[DAT_date],day_30,tday),ALLEXCEPT( BICLID,BICLID[CLID_mobile],BICLID[CLID_NOM],BICLID[Alea])
            )
        ),
        [Purchase_Between_40_59] <> 0
            && [Last30DaysPurchase] = 0
    )
)

 

 

It works when I select a month however when I try to select a specific date it shows Blank value.

 

Could you help me and tell me what I did wrong ?

 

Kind regards,

 

Mohammad

  • Anonymous's avatar
    Anonymous
    5 years ago

    Anonymous 

     

    IF(HASONEVALUE(BIDAT[DAT_mois_nom])
    You have used month in the if condition, try change it to [Date_date] column.

     

     
    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Can you please show your data model and the structure of the BIDAT table?

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hello lbendlin, 

       

      Here it is : 

       

       

      It's a basic calendar table.

       

      Kind regards,

       

      Mohammad

      • Anonymous's avatar
        Anonymous
        Not applicable

        Anonymous 

         

        IF(HASONEVALUE(BIDAT[DAT_mois_nom])
        You have used month in the if condition, try change it to [Date_date] column.

         

         
        Paul Zheng _ Community Support Team
        If this post helps, please Accept it as the solution to help the other members find it more quickly.