Sales and customer measures for multiple years

[Anonymous]

Hello everyone,

The last few days I’m struggling with my first DAX-project.

I have 3 tables: dimCustomer, dimDates en factInvoices.

The facttable Invoices contains information of the years 2014-2017.

Our company wants insights in the following measures:

Sales measures (the amount of):

A –         No Sales in current year but in the previous year a Sales-amount (Lost Sales);

               For example: No Sales in 2016, Sales in 2015

C –         Sales in current year and Sales in previous year but Sales current year is less than previous year (Lost Sales)

               For example: Sales 2017 - Sales 2016 = negative

E –         Sales in current year and also Sales in previous year. Sales-amount is more than the Sales-amount in the previous year (Won Sales)

               For example: Sales 2016 – Sales 2015 = positive

G –         The Sales of new customers

               For example: Sales in 2017, no Sales in previous years.

I -           The amount of Sales of customers who had the same amount of Sales in the current year       as in the previous year.

Customer measures (the number of):

B –         Not a customer in the current year, in the previous year  a customer (lost customer);

D –         Customers in current year who had less Sales than in the previous year;

F –         The number of customers who had more Sales in the current year than in the previous year.

H –         The number of new customers.

J -           The number of customer who had the same amount of Sales in the current year as in the previous year.

Hopefully you understand what I mean.

Please help me with the writing of these measures. Do I need a supporting table for these measures?

Thanks in advance,

 With kind regards,

Cor

[v-jiascu-msft]

Hi @Anonymous,

Can you share a dummy pbix file please? We can't write DAX formulas without data and its structure.

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft,

I've made a new dummy model:

The report that I want to calculate is:

The relationship between the Calendar_table and the fact_table Invoices:

I hope you can help me.

Thanks in advance,

Cor

[Anonymous]

I’ve made a mistake in the report: the lost revenues % # Customers was wrong. The correct report should be:

Report_example2.JPG1067x521 125 KB

Chrs,

Cor