Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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)
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |