Time comparison multiple years

Hello everyone,

I’ve been struggling the past few days with DAX-formulas to compare several years.
On the internet there are several formulas but none of them gives me the result I want.
What’s the problem/challenge?

I’ve got 2 tables:

1. Calendar;
2. Model Fct_InvoiceLine, a fact-table with invoicelines from 2014-2018

I want to compare the number of customers and the amount of Sales during the years 2014-2018.

The result in the end:

• New customers: Number of customers and amount of Sales for each year
• Steady customers:

1. Won revenue: number of customers and amount Sales for each year
2. Steady: number of customers and amount Sales for each year
3. Lost revenue: number of customers and amount Sales for each year
   A steady customer is a customer with Sales in each year but the new Sales is more, less or the same as the previous year.

• Lost customers: Number of customers and amount of Sales for each year

My measures:

Total Turnover = SUM (‘Model Fct_InvoiceLine’[InvoiceLineTotalAmountExclVAT]

Number of Customers =
CALCULATE (
DISTINCTCOUNT ( ‘Model Fct_InvoiceLine’[Customernumber] );
FILTER ( ALL ( ‘Model Fct_InvoiceLine’[Customernumber] ); [Total Turnover] <> 0 )
)

The numbers from the measures mentioned above seems to be right.

At this point I need to go further but I’m lost.

I think that I have to make a virtual table with customernumber and sales for each year and after that I want to compare those tables with each other. I thought to use EXCEPT and SELECTEDCOLUMNS but I don’t know exactly how to use them.

Can you help me?

Thanks in advance,

Cor