Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |