How to use measures as a filter inside another measure?

[Anonymous]

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.

[Anonymous · Accepted solution]

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

[amitchandak]

@Anonymous , 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/ba-p/1361529