Get Start and End Dates for DATESBETWEEN from Another Table

[atacuboy]

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 🙂

[v-yetao1-msft · Accepted 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.

[atacuboy]

Thank you very much for the help, I really appreciate it! It works! 🙂

[atacuboy]

Forgot to add, what I currently have is 

Customer Orders = calculate(sum('Table 2'[Actual Orders]),DATESBETWEEN('Table 1'[Date of Visit],[Start Date],[End Date]))

Datesbetween not working yet as it seems I can't use dates from another table

No start and end dates