cancel
Showing results for
Did you mean:
Frequent Visitor

## How to use measures as a filter inside another measure?

Hello,

I am trying to calculate a YTD calculation for New customers and Lost customers. Conditions for New Customers and Lost Customers are:

1. New Customer (Flag) =

IF (AND (

OR ([Turnover Last Year] = 0, [Turnover Last Year] = Blank ()),

[Turnover Current Year] > 0), “New Customer”)

1. Lost Customer (Flag) =

IF (

AND ([Turnover Last year] > 0,

OR ([Turnover current year] <= 0, [Turnover current year] = BLANK ())

), “Lost Customers”)

Whereas [Turnover Last Year] and [Turnover Current Year] are the measures which are calculated from below sales tables :

 Customer Date Year Turnover Customer 3 1.1.2020 2020 € 5 400 Customer 3 1.4.2020 2020 € 3 900 Customer 3 1.5.2020 2020 € 322 Customer 3 1.6.2020 2020 € 0 ABC 1.10.2020 2020 € 194 ABC 1.11.2020 2020 € 0 ABC 1.11.2020 2020 € 1 858 ABC 1.12.2020 2020 € 0 ABC 1.12.2020 2020 € 890 ABC 1.12.2020 2020 € 907 XYZ 1.4.2020 2020 -€ 2 005 XYZ 1.4.2020 2020 € 2 005 XYZ 1.5.2020 2020 -€ 6 016 XYZ 1.5.2020 2020 € 0 XYZ 1.5.2020 2020 € 6 016 XYZ 1.6.2020 2020 € 0 XYZ 1.6.2020 2020 € 6 094 XYZ 1.7.2020 2020 -€ 2 902 XYZ 1.7.2020 2020 € 0 XYZ 1.7.2020 2020 € 6 094 Customer 2 1.1.2021 2021 € 600 Customer 2 1.2.2021 2021 € 3 780 ABC 1.1.2021 2021 € 1 328 ABC 1.1.2021 2021 € 2 611 ABC 1.2.2021 2021 € 0 ABC 1.2.2021 2021 € 2 374 ABC 1.2.2021 2021 € 3 144 ABC 1.3.2021 2021 € 0 ABC 1.3.2021 2021 € 1 319 Customer 1 1.1.2021 2021 € 335 Customer 1 1.1.2021 2021 € 503 Customer 1 1.1.2021 2021 € 670 Customer 1 1.1.2021 2021 € 1 151 Customer 1 1.1.2021 2021 € 1 508 Customer 1 1.1.2021 2021 € 1 676 Customer 1 1.1.2021 2021 € 1 966 Customer 1 1.1.2021 2021 € 2 011 Customer 1 1.1.2021 2021 € 3 507 Customer 1 1.2.2021 2021 € 0 Customer 1 1.2.2021 2021 € 6 Customer 1 1.2.2021 2021 € 11 Customer 1 1.2.2021 2021 € 22 Customer 1 1.2.2021 2021 € 28 Customer 1 1.2.2021 2021 € 45 Customer 1 1.2.2021 2021 € 56 Customer 1 1.2.2021 2021 € 61 Customer 1 1.2.2021 2021 € 67 Customer 1 1.2.2021 2021 € 101 Customer 1 1.2.2021 2021 € 134 Customer 1 1.2.2021 2021 € 1 676 Customer 1 1.3.2021 2021 € 0 Customer 1 1.3.2021 2021 € 4 Customer 1 1.3.2021 2021 € 6 Customer 1 1.3.2021 2021 € 11 Customer 1 1.3.2021 2021 € 22 Customer 1 1.3.2021 2021 € 34 Customer 1 1.3.2021 2021 € 78 Customer 1 1.3.2021 2021 € 335 Customer 1 1.3.2021 2021 € 1 173 Customer 1 1.4.2021 2021 € 61 Customer 1 1.4.2021 2021 € 101 Customer 1 1.4.2021 2021 € 112 Customer 1 1.4.2021 2021 € 123 Customer 1 1.4.2021 2021 € 335 Customer 1 1.4.2021 2021 € 838 Customer 1 1.4.2021 2021 € 1 005 Customer 1 1.4.2021 2021 € 1 173 Customer 1 1.4.2021 2021 € 1 329 Customer 1 1.4.2021 2021 € 1 452

Expected Result :

New Customer YTD  and Lost Customer YTD

Any help would be really appreciated.

1 ACCEPTED SOLUTION
Resolver I

Turnover of YTD (NewCusomers) = CALCULATE(TOTALYTD([Turnover],'Date Table'[Date]),filter(Table_name, [New Customer (Flag)] = "New Customer"))

2 REPLIES 2
Resolver I

Turnover of YTD (NewCusomers) = CALCULATE(TOTALYTD([Turnover],'Date Table'[Date]),filter(Table_name, [New Customer (Flag)] = "New Customer"))

Super User IV

@ankibano , in the last one use filter(Table , [New Customer (Flag)] = "New Customer")

Small changes

New Customer (Flag) =

IF (AND (

OR ([Turnover Last Year] = 0, isblank([Turnover Last Year])),

[Turnover Current Year] > 0), “New Customer”, blank())

Lost Customer (Flag) =

IF (

AND ([Turnover Last year] > 0,

OR ([Turnover current year] <= 0, isblank([Turnover current year] ))

), “Lost Customers”, blank())

Refer my blog change month to year

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

Proud to be a Super User!

Announcements