Forum Discussion
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
- Anonymous5 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
- lbendlin
Super User
Can you please show your data model and the structure of the BIDAT table?
- AnonymousNot applicable
- AnonymousNot 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.