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.
Hi Everyone,
I'm trying to make a measure that checks if a customer's sales records tally with their actual purchases from the supplier for a specific period. Currently, I have 2 separate tables: one representing the customer's sales records and another for the record of their actual purchases from the supplier.
The result that I need for the measure based on the sample data below is 213K from Table 1 less 116K from Table 2 = 97K variance
***Please note that the actual tables contain data for several other customers and product types
Table 1: Customer's Sales Record for Product Soda A
The specific dates here represent instances where the supplier went to the customer to spot-check sales at that specific point in time. This means that for the red-highlighted date/row, the customer sold 213K worth of product Soda A from December 15, 2020 (the day after the last visit date) to March 4, 2021.
Customer | Date of Visit | Sales | Product |
A | Wednesday, August 29, 2018 | 282,581.38 | Soda A |
A | Sunday, February 3, 2019 | 856,529.54 | Soda A |
A | Monday, October 21, 2019 | 1,320,023.40 | Soda A |
A | Wednesday, January 22, 2020 | 507,254.76 | Soda A |
A | Wednesday, September 9, 2020 | 702,588.79 | Soda A |
A | Monday, December 14, 2020 | 265,926.59 | Soda A |
A | Thursday, March 4, 2021 | 213,139.60 | Soda A |
Table 2: Supplier's Records
These are the actual orders/purchases of the customer from the supplier. Based on the previous table, if we want to check the 213K amount, we need to add all orders from the Dec. 15-Mar 4 period, which in this table are the highlighted rows whose orders total 116,000.
Date | Actual Orders | Customer | Product |
Thursday, November 19, 2020 | 16000 | A | Soda A |
Sunday, December 6, 2020 | 16000 | A | Soda A |
Thursday, December 17, 2020 | 16000 | A | Soda A |
Saturday, January 9, 2021 | 16000 | A | Soda A |
Friday, January 22, 2021 | 20000 | A | Soda A |
Sunday, January 31, 2021 | 16000 | A | Soda A |
Saturday, February 6, 2021 | 16000 | A | Soda A |
Sunday, February 14, 2021 | 16000 | A | Soda A |
Thursday, February 25, 2021 | 16000 | A | Soda A |
Tuesday, March 9, 2021 | 16000 | A | Soda A |
Wednesday, March 24, 2021 | 16000 | A | Soda A |
Monday, April 5, 2021 | 16000 | A | Soda A |
Thank you very much for any assistance 🙂
Solved! Go to Solution.
Hi @atacuboy ,
Please correct me if I wrongly understood your question.
You said when you choose Thursday, March 4, 2021 in 'Customer's Sales Record for Product Soda A' , the date from table 'Supplier's Records' must between December 15, 2020 (the day after the last visit date) to March 4, 2021 from table 'Customer's Sales Record for Product Soda A' .
(1)So I create a slicer use 'Customer''s Sales Record for Product Soda A'[Date of Visit] field . Through the slicer to filter the date from table 'Supplier'’s Records' between 'Customer'’s Sales Record for Product Soda A' [Date of visit] && 'Customer’'s Sales Record for Product Soda A' [last date of visit] .
last date of visit = CALCULATE(MAX('Customer''s Sales Record for Product Soda A'[Date of Visit]),FILTER('Customer''s Sales Record for Product Soda A','Customer''s Sales Record for Product Soda A'[Date of Visit]<EARLIER('Customer''s Sales Record for Product Soda A'[Date of Visit])))
(2)Then create a column to sum the 'Supplier''s Records'[Actual Orders].
sum of actual orders = CALCULATE(SUM('Supplier''s Records'[Actual Orders]),FILTER('Supplier''s Records','Supplier''s Records'[Date]>'Customer''s Sales Record for Product Soda A'[last date of visit] && 'Supplier''s Records'[Date]<'Customer''s Sales Record for Product Soda A'[Date of Visit]))
(3)calculate the Sales variance between two tables .
diff = 'Customer''s Sales Record for Product Soda A'[Sales]-'Customer''s Sales Record for Product Soda A'[sum of actual orders]
I have attached the pbix file, you can refer to it.
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @atacuboy ,
Please correct me if I wrongly understood your question.
You said when you choose Thursday, March 4, 2021 in 'Customer's Sales Record for Product Soda A' , the date from table 'Supplier's Records' must between December 15, 2020 (the day after the last visit date) to March 4, 2021 from table 'Customer's Sales Record for Product Soda A' .
(1)So I create a slicer use 'Customer''s Sales Record for Product Soda A'[Date of Visit] field . Through the slicer to filter the date from table 'Supplier'’s Records' between 'Customer'’s Sales Record for Product Soda A' [Date of visit] && 'Customer’'s Sales Record for Product Soda A' [last date of visit] .
last date of visit = CALCULATE(MAX('Customer''s Sales Record for Product Soda A'[Date of Visit]),FILTER('Customer''s Sales Record for Product Soda A','Customer''s Sales Record for Product Soda A'[Date of Visit]<EARLIER('Customer''s Sales Record for Product Soda A'[Date of Visit])))
(2)Then create a column to sum the 'Supplier''s Records'[Actual Orders].
sum of actual orders = CALCULATE(SUM('Supplier''s Records'[Actual Orders]),FILTER('Supplier''s Records','Supplier''s Records'[Date]>'Customer''s Sales Record for Product Soda A'[last date of visit] && 'Supplier''s Records'[Date]<'Customer''s Sales Record for Product Soda A'[Date of Visit]))
(3)calculate the Sales variance between two tables .
diff = 'Customer''s Sales Record for Product Soda A'[Sales]-'Customer''s Sales Record for Product Soda A'[sum of actual orders]
I have attached the pbix file, you can refer to it.
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much for the help, I really appreciate it! It works! 🙂
Forgot to add, what I currently have is
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 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |