Forum Discussion

dwel0120's avatar
dwel0120
Helper III
5 years ago

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 c...
amitchandak's avatar
amitchandak
Super User
5 years ago

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=pnAesWxYgJ8

dwel0120's avatar
dwel0120
Helper III
5 years ago

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))