WOW Week over week change based on multiple slicer selection not working

[Junaid11]

Hello,
I have below data. I want to do Week over week % change and using below formulas: I have creted the mesuare with date table as well as single table but when I use slicer of Event or type it does not filter. If I make multiple selection in the slicer then it doesn ot work even. 

Visits	Revenue	Date	Event	Type
45	$345	Nov 3, 2022	Holiday	Social
34	$6	Nov 10, 2022	Black Friday	Web
7	$56	Nov 10, 2022	Holiday	Web
5	$567	Nov 17, 2022	Black Friday	Social
5	$34	Nov 17, 2022	Holiday	Web
23	$4,757	Nov 24, 2022	Black Friday	Social
456	$457	Nov 24, 2022	Cyber Week	Web
45	$34	Nov 24, 2022	Holiday	Social
76	$567	Dec 1, 2022	Warehouse Blowout	Social
768	$7	Dec 1, 2022	Holiday	Social
45	$456	Dec 8, 2022	Holiday	Web
90	$567	Dec 15, 2022	Holiday	Social
54	$45	Dec 22, 2022	Boxing Week	Web
4	$23	Dec 22, 2022	Holiday	Web

new Created columns are:
Week Start date = 'Table'[Date]+-1*WEEKDAY('Table'[Date],2)+1
Week End date = 'v'[Date]+ 7-1*WEEKDAY('Table'[Date],2)
Week Rank = RANKX(all('Date'),'Table'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Table'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('Table'[Visits]), FILTER(ALL('Table'),'Table'[Week Rank]=max('Table'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Visits]), FILTER(ALL('Table'),'Table'[Week Rank]=max('Table'[Week Rank])-1))
WOW = (This Week-Last Week)/Last Week

[v-xinruzhu-msft · Accepted solution]

Hi @Junaid11 

You can create a new column in table:

Weeknum = WEEKNUM('Table'[Date],2)

then create a measure

Measure = var _thisweek=SUMX(FILTER(ALLSELECTED('Table'),[Weeknum]=MAX([Weeknum])),[Visits])
var _lastweek=SUMX(FILTER(ALLSELECTED('Table'),[Weeknum]=MAX([Weeknum])-1),[Visits])
return DIVIDE(_thisweek-_lastweek,_lastweek)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

You should create a Calendar Table with calculated column formulas for Year, Month number, Month name and week number.  Sort the Month name column by the Month number column.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag Year and week number from the Calendar Table.  Write these measures

V = sum('Table'[Visits])

V last week = calculate([V],datesbetween(calendar[date],min(calendar[date])-7,max(calendar[date])-7))

WoW = divide(([V]-[V last week]),[V last week])

Hope this helps.