Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
=VAR CurrentYear = YEAR(MAX('Sales'[Completion Date]))
VAR LastYear = CurrentYear - 1
RETURN
IF(
CALCULATE(
COUNTROWS('Sales'),
FILTER(
'Sales',
YEAR('Sales'[Completion Date]) = LastYear
)
) > 0
&&
CALCULATE(
COUNTROWS('Sales'),
FILTER(
'Sales',
YEAR('Sales'[Completion Date]) = CurrentYear
)
) = 0,
1,
0
)
Customer Name | Completion Date |
Customer 1 | 1/13/2022 |
Customer 1 | 7/25/2023 |
Customer 1 | 5/11/2023 |
Customer 1 | 5/25/2023 |
Customer 1 | 6/8/2023 |
Customer 1 | 5/20/2022 |
Customer 2
| 5/9/2022 |
Customer 2 | 5/16/2022 |
Customer 2 | 6/13/2022 |
Customer 2 | 7/9/2022 |
Customer 2 | 8/8/2022 |
Customer 2 | 9/6/2022 |
Customer 2 | 10/1/2022 |
This formula just returns 0 as if I have no lost customers. It should be
1 = Made purchase in 2022 / no purchase 2023
otherwise = 0
Any help?
Solved! Go to Solution.
Try changing your first variable to:
VAR CurrentYear =
YEAR(
MAXX(
ALL( 'Sales'),
[Completion Date]
)
)
It worked. Thanks so much.
Try changing your first variable to:
VAR CurrentYear =
YEAR(
MAXX(
ALL( 'Sales'),
[Completion Date]
)
)
User | Count |
---|---|
91 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
72 | |
60 | |
59 |