Sum of value from dinamic dates from two tables

[Anonymous]

Hello

I want to sum a total of real sales between dates contained on other table; however, with the formula below, I always get the total sales for all dates. 

Table 1
Date of request	Stimated Volume Order for the month	Product
15/04/2020	50	A
15/04/2020	100	A
15/04/2020	80	B
25/04/2020	100	A
25/04/2020	40	B
25/04/2020	50	B
10/05/2020	100	A
10/05/2020	200	B
15/05/2020	500	A
15/05/2020	300	B

Table 2
Date of sale	Real sales	Product
01/04/2020	10	A
02/04/2020	3	B
03/04/2020	7	A
04/04/2020	13	B
05/04/2020	5	A
06/04/2020	4	B
07/04/2020	19	A
08/04/2020	13	B
09/04/2020	7	A
10/04/2020	18	B
11/04/2020	12	A
12/04/2020	11	B
13/04/2020	16	A
14/04/2020	6	B
15/04/2020	19	A
16/04/2020	8	B
17/04/2020	4	A
18/04/2020	19	B
19/04/2020	13	A
20/04/2020	7	B
21/04/2020	18	A
22/04/2020	12	B
23/04/2020	11	A
24/04/2020	16	B
25/04/2020	6	A
26/04/2020	19	B
27/04/2020	8	A
28/04/2020	7	B
29/04/2020	18	A
30/04/2020	12	B
01/05/2020	19	A
02/05/2020	8	B
03/05/2020	4	A
04/05/2020	19	B
05/05/2020	13	A
06/05/2020	7	B
07/05/2020	18	B
08/05/2020	12	A
09/05/2020	11	B
10/05/2020	4	A
11/05/2020	19	B
12/05/2020	13	A
13/05/2020	7	B
14/05/2020	18	B

Result desired	Sales as of Date of Request for April	Sales as of Date of Request for April	Sales as of Date of Request for May	Sales as of Date of Request for May
Vol Sales by product	15/04/2020	25/04/2020	10/05/2020	15/05/2020
A	95	52	52	13
B	68	62	63	44
Total	163	114	115	57

measure = calculate(

var minDate = STARTOFMONTH(Table 1 [Date])

var maxDate = CALCULATE(Max(Table 1 [Date]),ALLSELECTED(Table 1 [Date]))

return

Calculate (

sum(Table 2[Real Sales]),Table 1 [Date]=maxDate))

ERROR	Sales as of Date of Request for April	Sales as of Date of Request for April	Sales as of Date of Request for May	Sales as of Date of Request for May
Vol Sales by product	15/04/2020	25/04/2020	10/05/2020	15/05/2020
A	147	147	65	65
B	130	130	107	107
Total	277	277	172	172

Thanks in advance for your help 

[v-deddai1-msft]

Hi @Anonymous ,

Would you please explain more about your desired output? Please try the following measure:

measure = calculate(
var minDate = STARTOFMONTH(Table 1 [Date])
var maxDate = Max(Table 1 [Date])

return
Calculate (
sum(Table 2[Real Sales]),Table 1 [Date]>=minDate && Table 1 [Date]<=maxDate)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Greg_Deckler · Accepted solution]

@Anonymous  Guessing but seems like it should be something like:

measure =
var minDate = STARTOFMONTH(Table 1 [Date])
var maxDate = CALCULATE(Max(Table 1 [Date]),ALLSELECTED(Table 1 [Date]))

return
Calculate (
sum('Table 2'[Real Sales]),'Table 2'[Date]>=minDate && 'Table 2'[Date]<=maxDate))

I personally wouldn't use STARTOFMONTH as DATE(YEAR(MAX(['Table 1'[Date])),MONTH(MAX('Table 1'[Date])),1) is the same thing and I do not like TI functions at all.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008