# Problems with the Sales by Week comparison to prior year formula

I watched Sam’s Calculate Previous Week’s Sales video and copied the code for a similar calculation in my file. When I go to bring the [PriorSales] measure into my visual, I receive an error that the data couldn’t be loaded due to improper combination of formats (text with number). [See screenshot] Would this be because the SELECTEDVALUE function returns a text value for Week and Year, and then the operation is trying to subtract -1 from the text value and that’s returning the error? I’ve checked the formats on all my measures and they are Whole number. I feel like I’m the only one experiencing this error when in the video and elsewhere on this forum the exact same coding is used without throwing the error - disconcerting, but I will plod on. . . !

Screenshot 5-1-20.pdf (169.0 KB)

can you in your formula wrap the “currentYear - 1” in with “Value” function

if you can post the pbix it will be easier to fix though

https://docs.microsoft.com/en-us/dax/value-function-dax

cancelling my last comment

this is a possible solution

Year YTD SALES through today=
VAR SalesYTD = TOTALYTD([Total Sales], ‘Date’[Date])
VAR SalesPreviousYTD = CALCULATE(SalesYTD, SAMEPERIODLASTYEAR(‘Date’[Date]))
RETURN
SalesPreviousYTD

I also went ahead and fixed your origional query . pls check the solution below. Note the value I added for the comparison.
Weekly Sales =
var previousWeek = SELECTEDVALUE(‘Date’[WeekNumber]) - 1
var currentYear = SELECTEDVALUE(‘Date’[Year])
var priorYear = SELECTEDVALUE(‘Date’[Year]) - 1
var MaxWeekNumber = CALCULATE(Max(‘Date’[WeekNumber]), All(‘Date’))

``````return
Sumx(
Filter(All('Date'),
If(previousWeek = 1,
'Date'[WeekNumber] = MaxWeekNumber && Value(('Date'[Year])) = priorYear,
'Date'[WeekNumber] = previousWeek && 'Date'[Year] = currentYear
)
),
[Total Sales]
)
``````

pls let me know if you have any additional question on it

Check the datatype of the Year in the Date table in your visual and change that to number, then it should work.

1 Like

Hi @kjssdca, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

I’m sorry but your query edits were off as to the previousWeek number. I couldn’t use your solution.

No prob - all you have to do is - replace previousWeek with currentWeek

Weekly Sales =
var currentWeek = SELECTEDVALUE(‘Date’[WeekNumber])
var currentYear = SELECTEDVALUE(‘Date’[Year])
var priorYear = SELECTEDVALUE(‘Date’[Year]) - 1
var MaxWeekNumber = CALCULATE(Max(‘Date’[WeekNumber]), All(‘Date’))

``````return
Sumx(
Filter(All('Date'),
If(currentWeek = 1,
'Date'[WeekNumber] = MaxWeekNumber && Value(('Date'[Year])) = priorYear,
'Date'[WeekNumber] = previousWeek && 'Date'[Year] = currentYear
)
),
[Total Sales]
)
``````