Lost Customers measure


#1

Hi Sam,

I can’t figure out why my Lost Customer measure won’t sum up. The criteria for lost customer (by selected month) is 1.) if revenue in selected month <= 0, 2.) if revenue in prior period > 0, and 3.) if future revenue <= 0. Value is 1 if all criteria are true. I seem to be getting the expected result (1 or blank) but I can’t sum it up. What am I missing?

Lost Customers =
VAR HasRevenuePrevPeriod = [Previous Period Revenue] > 0
VAR HasNoCurrentPeriodRevenue = [Revenue] <= 0
VAR HasNoFutureRevenue = [Future Periods Revenue] <= 0
RETURN

IF(HasRevenuePrevPeriod && HasNoCurrentPeriodRevenue && HasNoFutureRevenue
,1
,BLANK())

Thanks,

Ferdinand


#2

Hi Sam,

I found a way to sum up my Lost Customer measure. I made the Customer table its home table (it used to be in Key Measures table) and created a SUMX measure that sums it up from its new home table. I would still be interested to know other ways of achieving the same goal.

Best regards,

Ferdinand


#3

I just found out the home table didn’t matter. The sumx formula still produced the correct result when I moved it back to the Key Measures table. I just had to make sure I reference the customer table in the sumx formula.

Regards,

Ferdinand


#4

Ok yes that great.

I was reading through ‘home’ table and wasn’t sure what you meant. Looks like you understand it now.

Chrs
Sam