Dear Community,
I have the following metric:
BillsMonths.'MRR SS'[BY:BillsMonths.Mail,BillsMonths.Month]
I am trying to create another metric based on the previous metric, with the following formula:
COUNTOF('Billsmonth Spread')[BY SUM: 'Data Hub'::BillsMonths.Month]
And I get the next error in the playground formula
And if I create the metric with the dimension I get the same number for every month
Would you please point me in the right direction?
Thank you very much in advance!
Jose
Best answer by francois
Hi Jose,
You’re on the right track!
Here’s what I’ve built based on your formula. It’s different metrics but the logic should be the same.
The MOVINGSUM helps get the added value for last three months, so a blank value should mean a blank value on each of the last three months.
Please also note that IF does not need a third argument, you can just leave it blank which will be more efficient.
+12Hi Jose,
If you run COUNTOF in the playground you’ll see it removes the month dimension.
You can do 'Billsmonth Spread'[BY count: Month] to get what you want
+13Hi Jose,
COUNTOF will remove all dimensions. If you want to count cells, you can however use BY COUNT to get to your result.
Hope this helps!
That worked well with the [BY count: Month] ! Thanks!
If I want to do a formula that counts the previous months also? like I want to count how many are not blank but where blank in the previous 3 months, to identify new users.
On excel would be an easy formula like this =COUNTIFS(CK$36:CK$6500;"<>0";CJ$36:CJ$6500;0;CI$36:CI$6500;0)
Thank you very much in advance!
Jose
I did the following formula and it seemed to work:
IF('Billsmonth Spread'[SELECT: 'Data Hub'::Month-3]=0 AND 'Billsmonth Spread'[SELECT: 'Data Hub'::Month-2]=0 AND'Billsmonth Spread'[SELECT: 'Data Hub'::Month-1]=0 AND 'Billsmonth Spread'[SELECT: 'Data Hub'::Month]<>0,1,0)
+13Hi Jose,
You’re on the right track!
Here’s what I’ve built based on your formula. It’s different metrics but the logic should be the same.
The MOVINGSUM helps get the added value for last three months, so a blank value should mean a blank value on each of the last three months.
Please also note that IF does not need a third argument, you can just leave it blank which will be more efficient.
Hello Francois;
It doesnt work well the formula of ISDEFINED because I 0s instead of blanks.
I tried adapting it but it did not work:
IF(ISBLANK(MOVINGSUM('Billsmonth Spread',3)[SELECT:'Data Hub'::Month-1])AND 'Billsmonth Spread'[SELECT:'Data Hub'::Month]<>0;1)
I got Syntax error: cannot validate formula
+13I don’t get why you added a SELECT in that last part - it’s probably the source of the error.
I think your formula should be
IF(MOVINGSUM(ABS('Billsmonth Spread'),3)[SELECT:'Data Hub'::Month-1] = 0 AND 'Billsmonth Spread' > 0;1)
I’ve added ABS to make sure two consecutive values don’t cancel out.
Hi Francois!
I tried doing the formula as you suggested, but I still get Syntax error: cannot validate formula
Thank you!!
+13I didn’t read the formula completely… You have a semicolon instead of just a comma.IF(MOVINGSUM(ABS('Billsmonth Spread'),3)[SELECT:'Data Hub'::Month-1] = 0 AND 'Billsmonth Spread' > 0;1)
IF(MOVINGSUM(ABS('Billsmonth Spread'),3)[SELECT:'Data Hub'::Month-1] = 0 AND 'Billsmonth Spread' > 0,1)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
