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:

- Calendar;
- 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:

- Won revenue: number of customers and amount Sales for each year
- Steady: number of customers and amount Sales for each year
- 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