Next Week Projected Sales

gsmith_eqd — Thu, 02 Feb 2023 22:47:38 GMT

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!

Re: Next Week Projected Sales

FreemanZ — Fri, 03 Feb 2023 00:01:07 GMT

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)

topic Re: Next Week Projected Sales in DAX Commands and Tips

Next Week Projected Sales

Re: Next Week Projected Sales