topic Re: Compare week-1 with week-2 in DAX Commands and Tips

Compare week-1 with week-2

Anonymous — Wed, 08 Jan 2020 17:33:11 GMT

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?

Re: Compare week-1 with week-2

az38 — Wed, 08 Jan 2020 17:44:19 GMT

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

Re: Compare week-1 with week-2

Anonymous — Wed, 08 Jan 2020 17:46:55 GMT

Week = WEEKNUM('Date'[Date])

Re: Compare week-1 with week-2

az38 — Wed, 08 Jan 2020 18:17:24 GMT

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

Re: Compare week-1 with week-2

Anonymous — Wed, 08 Jan 2020 18:22:52 GMT

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

Re: Compare week-1 with week-2

az38 — Wed, 08 Jan 2020 18:24:54 GMT

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

Re: Compare week-1 with week-2

Anonymous — Wed, 08 Jan 2020 18:42:39 GMT

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

Re: Compare week-1 with week-2

az38 — Wed, 08 Jan 2020 18:51:00 GMT

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