topic Re: Previous Week Change Rate in DAX Commands and Tips

Previous Week Change Rate

dwel0120 — Thu, 22 Jul 2021 20:28:07 GMT

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

Re: Previous Week Change Rate

amitchandak — Fri, 23 Jul 2021 05:44:34 GMT

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

Re: Previous Week Change Rate

Anonymous — Fri, 23 Jul 2021 13:08:19 GMT

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.

Re: Previous Week Change Rate

dwel0120 — Mon, 26 Jul 2021 14:01:49 GMT

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