Measure to calculate which customers are New/Current/Lost

[Mdobbels]

I would need a measure to divide my clients into the categories: Lost, New and Current.

The definitions for these categories are:

New= No Revenue before this year
Lost= No Revenue in the last 3 months (and not new)

Current= Not New nor Lost

Extra difficulty. As the information is coming in after closing we should actually make it so that "Before last year" is actually in the year before the last revenue was uploaded.  (The December 2021 information is in the refresh around January 6th, so as long as the January 2022 is not uploaded, before last year should be 2020) 

The same difficulty also applies to the definition of "Last 3 months)

Thank you in advance.

[Greg_Deckler]

@Mdobbels Check out New and Returning Customers: https://community.powerbi.com/t5/Quick-Measures-Gallery/New-and-Returning-Customers/m-p/168297#M13

I also have a many similar calculations for customers in DAX Cookbook. Chapter 6: PacktPublishing/DAX-Cookbook: DAX Cookbook, Published by Packt (github.com)

[Mdobbels]

Hi @Greg_Deckler ,

Is it in your solution possible to see which clients are the new, lost and current customers?

Because I don't want to count them, I want to know which customers are new or lost so that Sales can act upon this information.

Kind regards,

Matthias