Next Week Projected Sales

[gsmith_eqd]

Hello,

I'm trying to create a measure for a report that displays the sum of the sales amount for the following week by filtering based on the shipdate column. There is no error appearing in the formula, but the field is still coming up blank and I can't figure out why.

Here's the total sales measure:

TotalSales = SUM(SalesOrderLine[Amount])

And here's the measure coming up blank:

EstimatedInvoicing =
VAR __ThisDate = TODAY()
VAR __ThisWeek = WEEKNUM(__ThisDate, 1)
VAR __NextWeek = __ThisWeek + 1
VAR __ThisYear = YEAR(__ThisDate)
RETURN

CALCULATE([TotalSales], WEEKNUM(SalesOrderLine[ShipDate]) = __NextWeek && YEAR(SalesOrderLine[ShipDate]) = __ThisYear)

I realize I will probably have to build in an exception for end of year scenarios, but for now _ThisWeek + 1 should be appropriate to give me a populated field. I've tried rephrasing __NextWeek as 'WEEKNUM(__ThisDate, 1) +1' but it didn't make a difference. 

As an experiment I tried writing this without the variables and what I found was interesting.

EstimatedInvoicing TEST =

CALCULATE([TotalSales], WEEKNUM(SalesOrderLine[ShipDate]) = WEEKNUM(TODAY()) + 1 && YEAR(SalesOrderLine[ShipDate]) = YEAR(TODAY()))

The measure above gave a blank column. However, when I removed the year filter clause, it was no longer blank. It also wasn't the value I was looking for, but I'm wondering if this is any evidence to help solve this problem.

Any help is appreciated!

[FreemanZ · Accepted solution]

hi @gsmith_eqd 

try first adjust the return type of the 2nd WEEKNUM:

EstimatedInvoicing =

VAR __ThisDate = TODAY()

VAR __ThisWeek = WEEKNUM(__ThisDate, 1)

VAR __NextWeek = __ThisWeek + 1

VAR __ThisYear = YEAR(__ThisDate)

RETURN

CALCULATE([TotalSales], WEEKNUM(SalesOrderLine[ShipDate], 1) = __NextWeek && YEAR(SalesOrderLine[ShipDate]) = __ThisYear)