Forum Discussion
Helper VWOW Week over week change based on multiple slicer selection not working
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
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.
3 Replies
- Ashish_Mathur
Super User
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.
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.
I have same issue that slicer is not showing the results of my WOW% measure in a table
Below is my measures that doesn't work.
SalesLastWeek =
calculate (
sum( table[sales]),
filter(
all( table[week]),
table[week]=max(table[week])-1))
WOW% = divide (sales this week - sales lastweek, sales lastweek)
please help me on this
