cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
corvada Member
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?

 

Thanks in advance,

 

 With kind regards,

 

Cor

3 REPLIES 3
v-jiascu-msft Super Contributor
Super Contributor

Re: Sales and customer measures for multiple years

Hi @corvada,

 

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.
Highlighted
corvada Member
Member

Re: Sales and customer measures for multiple years

Hi @v-jiascu-msft,

 

I've made a new dummy model:

 

Invoices_example.JPG

 

The report that I want to calculate is:

 

Report_example.JPG

 

The relationship between the Calendar_table and the fact_table Invoices:

 

Relationships_example.JPG

 

I hope you can help me.

 

Thanks in advance,

 

Cor

 

 

corvada Member
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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 358 members 3,052 guests
Please welcome our newest community members: