Previous Week Change Rate

[dwel0120]

I'm trying to do a previous week to current week rate. I have these columns: WEEKOF, WKNM (week number), WITHDRAWALRATE, WEEKRATE, and YEAR. I also have a measure WITHDRAWALRATEMEASURE. 

I have come up with a formula but it is not giving me the correct rate. 

PreviousWeekChange =

VAR PreviousWeek =

CALCULATE (

'TABLE1'[WITHDRAWALRateMeasure],

FILTER (

ALL (TABLE1),

'TABLE1'[WKNM]

= MAX ( 'TABLE1'[WKNM] ) -1

)

)

RETURN

IF (

MAX('TABLE1'[WKNM]) =1,

BLANK(),

DIVIDE ( ( TABLE1[WITHDRAWALRateMeasure] - PREVIOUSWEEK), PREVIOUSWEEK))

[Anonymous]

Instead of using YearWeekNum, I suggest to have a column "WeekStartDate" . It will make your life easier for across the years as you would have to simply subtract 7 days to get the Previous week.

[amitchandak]

@dwel0120 , best way to work with week is to have separate week table, Prefer to have year week Table, with Rank on Year Week (YYYYWW)

example

if WITHDRAWALRateMeasure is the measure, you do not need table name, else use sum(Table[WITHDRAWALRateMeasure])

PreviousWeekChange =
VAR PreviousWeek =
CALCULATE (
[WITHDRAWALRateMeasure],
FILTER (
ALL (WEEK),
'WEEK'[WKNM]
= MAX ( 'WEEK'[WKNM] ) -1
)
)
RETURN
IF (
MAX('WEEK'[WKNM]) =1,
BLANK(),
DIVIDE ( ( [WITHDRAWALRateMeasure] - PREVIOUSWEEK), PREVIOUSWEEK))

Also, refer to my blog how to deal week with help from rank if needed

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[dwel0120]

@amitchandak That didn't work for me. I can't figure out what is happening. Screenshot of table below and formula. I also created a week table like the one you provided a link to but that did now work either. I even created the relationship between the TABLE1 and the Week Table. 

PreviousWeekChange =

VAR PreviousWeek =

CALCULATE (

[WITHDRAWALRateMeasure],

FILTER (

ALL ('TABLE1'),

'TABLE1'[WKNM]

= MAX ( 'TABLE1'[WKNM] ) -1

)

)

RETURN

IF (

MAX('TABLE1'[WKNM]) =1,

BLANK(),

DIVIDE ( ( [WITHDRAWALRateMeasure] - PREVIOUSWEEK), PREVIOUSWEEK))