Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
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
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
When I add that to the visual I get another error. Any ideas?
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
Hi @tidal_whale
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
Week = WEEKNUM('Date'[Date])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |