cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paulfink
Post Patron
Post Patron

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.

 

image.png

 

 

 

Left Date is Start Date and Righr Date is End Date.

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User IV
Super User IV

@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 format

 

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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

 

 

With the Start date you want 1 week and the end date you want another week ?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors