Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Hi @Anonymous,
Can you share a dummy pbix file please? We can't write DAX formulas without data and its structure.
Best Regards,
Dale
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
User | Count |
---|---|
94 | |
86 | |
78 | |
69 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |