Week over week compiling sales.

[DJSwezey]

I have a table 'GoalsByWeek' that contains [Sales Goal Amount], [Actual Sales Amount] and [WeekOf].

I have created another table called 'WeekOrder' that contains [WeekOf] and [Week Num] from 'GoalsByWeek' and a "Total" value added in duplicate of each [WeekOf] so I can relocate the TOTAL columns into the front of my Matrix. 

I related this table's [Week Num] to the 'GoalsByWeek'[Week Num]. This allows me to pull in 'WeekOrder'[WeekOf] into my matrix and reorder the columns to have the Total as the first column.

Originally (before I relocated the TOTAL columns to the left side of the matrix), I had a measure setup to calculate the sum of [Sales Goal Amount] and [Actual Sales Amount] for each week and then another measure for [Sales Goal Amount] and [Actual Sales Amount] week over week. 

EX:

You can see week 1 actual sales = 90, week 2 actual sales = 50, thus week 2 actual sales week over week should be 140.

The problem I am having is, with the new relationship between the 'WeekOrder' table and 'GoalsByWeek' my Week over Week results don't compile. They are simply each weeks sum(yellow). No idea why this calculation isn't working anymore... 

My week over week calculation:

WOW Sales Amount = CALCULATE(sum('GoalsByWeek'[Actual Sales Amount]),DATESBETWEEN('GoalByWeek'[WeekOf],max('GoalsByWeek'[WeekOf])-1000,max('GoalsByWeek'[WeekOf])))

[Mahesh0016]

@DJSwezey please try this

WOW Sales Amount = CALCULATE(sum('GoalsByWeek'[Actual Sales Amount]),DATESBETWEEN(All('GoalByWeek'[WeekOf]),max('GoalsByWeek'[WeekOf])-1000,max('GoalsByWeek'[WeekOf])))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DJSwezey]

I unfortunately get this error when I use that measure: