cancel
Showing results for
Did you mean:
Member

## Sales and customer measures for multiple years

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?

With kind regards,

Cor

3 REPLIES 3
Super Contributor

## Re: Sales and customer measures for multiple years

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

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Sales and customer measures for multiple years

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.

Cor

Member

## Re: Sales and customer measures for multiple years

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

Chrs,

Cor

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 60 members 1,283 guests
Recent signins: