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.
Hello,
I am trying to calculate a YTD calculation for New customers and Lost customers. Conditions for New Customers and Lost Customers are:
IF (AND (
OR ([Turnover Last Year] = 0, [Turnover Last Year] = Blank ()),
[Turnover Current Year] > 0), “New Customer”)
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.
Solved! Go to Solution.
Turnover of YTD (NewCusomers) = CALCULATE(TOTALYTD([Turnover],'Date Table'[Date]),filter(Table_name, [New Customer (Flag)] = "New Customer"))
Turnover of YTD (NewCusomers) = CALCULATE(TOTALYTD([Turnover],'Date Table'[Date]),filter(Table_name, [New Customer (Flag)] = "New Customer"))
@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
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |