Forum Discussion
Previous Week Change Rate
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.
3 Replies
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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8amitchandak 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))RETURNIF (MAX('TABLE1'[WKNM]) =1,BLANK(),DIVIDE ( ( [WITHDRAWALRateMeasure] - PREVIOUSWEEK), PREVIOUSWEEK))
