Problems with the Sales by Week comparison to prior year formula

DAX DAX Calculations
1

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)

2

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

3

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

4

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

1 Like
5

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!

6

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

7

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