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
Highlighted
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.
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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

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: 183 members 2,023 guests
Please welcome our newest community members: