QTD measure not working after I join Date & Invoice tables

[Roseventura]

Can someone explain why my Quarter to Date Sales goes from $880.84K (which is correct) to $15.71K (which represents ONLY today’s sales) when I join my Calendar table to my Sales table?  If I don’t create a relationship (join) between the two tables, my QTD total is correct.  When I join the two tables (on CADATE > INVOICE_DATE), the total for the quarter is only TODAY’S total sales.

Here are the MEASURE functions I am using:

Quarter Sales = calculate(sum(PBI_Invoice_17[Total Extended Sales]), filter(PBI_Invoice_17,PBI_Invoice_17[Quarter] = CONCATENATE("Q",PBI_FSCAPF[Today Quarter])))

Today Quarter = lookupvalue(PBI_FSCAPF[CAFQTR],PBI_FSCAPF[LUDATE],PBI_FSCAPF[Current Date])

Current Date = today()

My Calendar Table (PBI_FSCAPF) looks like this (our format is CYYMMDD):

CADATE	CAFYR	CAFMON	CAFQTR	WeekdayNo	Weekday	LUDATE	LONGDATE	PADFQTR	YEAR-MO	YEAR-QTR
1170109	117	1	1	1	Monday	1/9/2017	20170109	Q1	2017-01	2017-Q1
1170110	117	1	1	2	Tuesday	1/10/2017	20170110	Q1	2017-01	2017-Q1
1170111	117	1	1	3	Wednesday	1/11/2017	20170111	Q1	2017-01	2017-Q1
1170112	117	1	1	4	Thursday	1/12/2017	20170112	Q1	2017-01	2017-Q1
1170113	117	1	1	5	Friday	1/13/2017	20170113	Q1	2017-01	2017-Q1
1170114	117	1	1	6	Saturday	1/14/2017	20170114	Q1	2017-01	2017-Q1
1170115	117	1	1	7	Sunday	1/15/2017	20170115	Q1	2017-01	2017-Q1

My Sales Table (PBI_Invoice_17) looks like this:

INVOICE_NUMBER	INVOICE_LN	INVOICE_DATE	Year-Mo	Year-Qtr	Quarter	Year	Ext Sales Amount
54778359	6	1170112	2017-01	2017-Q1	Q1	2017	302.4
54778359	7	1170112	2017-01	2017-Q1	Q1	2017	36
54778359	8	1170112	2017-01	2017-Q1	Q1	2017	80.5
54778359	9	1170112	2017-01	2017-Q1	Q1	2017	73.5
54778359	10	1170112	2017-01	2017-Q1	Q1	2017	97.6
54778359	11	1170112	2017-01	2017-Q1	Q1	2017	8
54778359	12	1170112	2017-01	2017-Q1	Q1	2017	159.8
54778359	13	1170112	2017-01	2017-Q1	Q1	2017	4.7
54778359	14	1170112	2017-01	2017-Q1	Q1	2017	4.7

The 3 highlighted numbers below are correct:

This one is correct

 When I join the numbers for YTD and MTD are correct, but QTD is not:

This QTD number is incorrect.

[Roseventura · Accepted solution]

I solved this as soon as I posted it!

I changed my Quarter Sales measure to this and it works now:

Quarter Sales = calculate(sum(PBI_Invoice_17[Total Extended Sales]),filter(PBI_Invoice_17,PBI_Invoice_17[Invoice Date]<=PBI_Invoice_17[Today]))

[v-huizhn-msft]

Hi @Roseventura,

I am very glad you have resolved your issue, please mark your solution as answer. So others can find it clearly. Thanks for understanding. 

Best Regards,
Angelia