Help on calculating a measure when selecting a specific date

[mmp]

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

[lbendlin]

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

[mmp]

Hello @lbendlin, 

Here it is : 

It's a basic calendar table.

Kind regards,

Mohammad

[V-pazhen-msft · Accepted solution]

@mmp 

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.