Filter Start and End Date using a Slicer

[paulfink]

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.

[v-shex-msft]

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

[amitchandak]

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