Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to calcluate;
"what were sales last month of customers who didn't purchase this month?"
This article was amazing, and a real game changer to help calculate returning, new and lost customer counts. As well as sales of new and returning customers:
Can anyone modify or add to the above article to cacluate sales of customers last month who didn't purchase this month? using same syntax and variables as in the article. thank you so much for your help!!
Solved! Go to Solution.
Lookvalue have a result which is not a boolean expression.
In your case, you have to add IF(lookvalue........) <> Blank()
When the result is blank the formula give you a value and when is not another value ("Y" or "")
Try with this:
IF
(
IF(SalesDetails<>12,
IsBlank(LookupValue(....)),
IsBlank(LookupValue(....))
),
"Y",""
)
This is all you need:
Lost customer sales = CALCULATE ( [SALES],PREVIOUSMONTH('DATE[DATE]),FILTER(CUSTOMER,[SALES]=0)
This is all you need:
Lost customer sales = CALCULATE ( [SALES],PREVIOUSMONTH('DATE[DATE]),FILTER(CUSTOMER,[SALES]=0)
Hi @cnpdx,
In your scenario, you can create a calculated column like below:
PurchaseLastmonthNotthis month =
VAR CKey = Sales[CutomerKey]
return
IF(IF('Sales'[Month]<>12,
LOOKUPVALUE( Sales[CutomerKey], Sales[CutomerKey], CKey, Sales[Month], Sales[Month] + 1),LOOKUPVALUE( Sales[CutomerKey], Sales[CutomerKey], CKey, Sales[Month], 1,Sales[Year],Sales[Year]+1))=BLANK(),"Y","")
Best Regards,
Qiuyun Yu
Hi @v-qiuyu-msft,
Thanks for responding!
I totally get your logic and I think it will work for my problem, I'm just having a syntax issue. Can you take a look:
Thanks for your help!
Hi @Vvelarde
Thanks, adding the ) worked, but now I get this new error. does anyone have a solution for that (pic)? The error says it doesn't like the customer key field (text)...
Lookvalue have a result which is not a boolean expression.
In your case, you have to add IF(lookvalue........) <> Blank()
When the result is blank the formula give you a value and when is not another value ("Y" or "")
i'm just not clear where that IF(lookvalue........) <> Blank() fits in to the whole formula. can you elaborate?
Try with this:
IF
(
IF(SalesDetails<>12,
IsBlank(LookupValue(....)),
IsBlank(LookupValue(....))
),
"Y",""
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |