Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Roseventura
Responsive Resident
Responsive Resident

QTD measure not working after I join Date & Invoice tables

 

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 correctThis one is correct

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

 

This QTD number is incorrect.This QTD number is incorrect.

 

 

 

 

1 ACCEPTED SOLUTION
Roseventura
Responsive Resident
Responsive Resident

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]))

View solution in original post

2 REPLIES 2
Roseventura
Responsive Resident
Responsive Resident

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]))

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.