Compare week-1 with week-2

[Anonymous]

Hi, this is my first post so please forgive me if this has been answered elsewhere (I did search before posting, honest).

I have a pricing file where I collect and compare various sources of prices for products.  I'm then comparing the price last calendar week (w-1) to the week prior (w-2).  I created a Date hierarchy to help with this.  Here is my formula:

WoW $ =

CALCULATE (

AVERAGE ( 'Main_Data_Feed'[Price Point] ),

FILTER (

ALL ( 'Date' ),

'Date'[Week]

= MIN ( 'Date'[Week] ) - 1

)

)

- CALCULATE (

AVERAGE ( 'Main_Data_Feed'[Price Point] ),

FILTER (

ALL ( 'Date' ),

'Date'[Week]

= MIN ( 'Date'[Week] ) - 2

)

)

As we're in 2020 week 2, I now see the flaw in my calculation as I want to compare week 1 to week 52 of 2019.  My current formula is showing blanks for w-2.

Any ideas how I can solve for this?

[az38]

@Anonymous 

try

WoW $ =
var _prevWeek = DATEADD('Date'[Date],-1, WEEK)
var _prev2Week = DATEADD(_prevWeek,-1, WEEK)

RETURN

CALCULATE (
AVERAGE ( 'Main_Data_Feed'[Price Point] ),
FILTER (
ALL ( 'Date' ),
YEAR('Date'[Date]) = YEAR(_prevWeek) && MONTH('Date'[Date]) = MONTH(_prevWeek)

)
)
- CALCULATE (
AVERAGE ( 'Main_Data_Feed'[Price Point] ),
FILTER (
ALL ( 'Date' ),
YEAR('Date'[Date]) = YEAR(_prev2Week) && MONTH('Date'[Date]) = MONTH(_prev2Week)
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Thanks for this but this returns an error:

'The last argument must be one of these keywords: DAY, MONTH, QUARTER, or YEAR.'

And refers to WEEK in lines 2 and 3

[az38]

@Anonymous 

no problem, sorry, didnt check

WoW $ =
var _prevWeek = DATEADD('Date'[Date],-7, DAY)
var _prev2Week = DATEADD(_prevWeek,-7, DAY)

RETURN

CALCULATE (
AVERAGE ( 'Main_Data_Feed'[Price Point] ),
FILTER (
ALL ( 'Date' ),
YEAR('Date'[Date]) = YEAR(_prevWeek) && MONTH('Date'[Date]) = MONTH(_prevWeek)

)
)
- CALCULATE (
AVERAGE ( 'Main_Data_Feed'[Price Point] ),
FILTER (
ALL ( 'Date' ),
YEAR('Date'[Date]) = YEAR(_prev2Week) && MONTH('Date'[Date]) = MONTH(_prev2Week)
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

When I add that to the visual I get another error.  Any ideas?

[az38]

@Anonymous 

try this or give us data model and data example please

WoW $ =
var _prevWeek = DATEADD(SELECTEDVALUE('Date'[Date]),-7, DAY)
var _prev2Week = DATEADD(_prevWeek,-7, DAY)

RETURN

CALCULATE (
AVERAGE ( 'Main_Data_Feed'[Price Point] ),
FILTER (
ALL ( 'Date' ),
YEAR('Date'[Date]) = YEAR(_prevWeek) && MONTH('Date'[Date]) = MONTH(_prevWeek)

)
)
- CALCULATE (
AVERAGE ( 'Main_Data_Feed'[Price Point] ),
FILTER (
ALL ( 'Date' ),
YEAR('Date'[Date]) = YEAR(_prev2Week) && MONTH('Date'[Date]) = MONTH(_prev2Week)
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38]

Hi @Anonymous 

how is look like your 'Date'[Week] ?

give as data example for this column please

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Week = WEEKNUM('Date'[Date])