Forum Discussion
Filter Start and End Date using a Slicer
HI guys,
ran into a problem.
I am looking to filter my data using a slicer but the slicer only affects the start and end date.
My report views the change of project progress over a weekly basis.
So far my data is looking start of last week to end of last week.
But now I want to have a slicer to change the date to whatever period i choose.
Currently my Change formula is the difference between the start and end of last week.
those formulas are simple LOOKUPVALUE
Progress at Start = LOOKUPVALUE(ProjectProgress[ProgressPercentage],ProjectProgress[ProjectNumber], Projects[ProjectNumber], ProjectProgress[ProgressDate], MIN(Date[Last Week]))
Progress at End = LOOKUPVALUE(ProjectProgress[ProgressPercentage],ProjectProgress[ProjectNumber], Projects[ProjectNumber], ProjectProgress[ProgressDate], MAX(Date[Last Week]))
all that is different is the MIN and MAX
i have tried using Calendar[Date] instead of last week and using the Date in a Slicer but the Change figures do not work.
What could i do in order to make the Slicer filter the date in which the Change column selects the progress at the Start and End date and finds the difference.
Left Date is Start Date and Righr Date is End Date.
2 Replies
paulfink , if you only want to use the end date as a reference point
You can get this week vs last week
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))Where Rank you can have in your date table by having these columns
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatPower 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=pnAesWxYgJ8With the Start date you want 1 week and the end date you want another week ?
HI paulfink,
You can also take a look at the following link about date range analysis if they suitable for your requirement.
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng
