Need help displaying values before/after a specific date

[Anonymous]

Hello There,

I am trying to create a way to show $ Sales Post and Pre a certain date, that date being 7/09/21. All my dates are at the weekly level with none missing. 

I have created static measures that are filtered to 4, 12, 24 weeks prior to 7/09/21. Example being " L4 $ Sales" = $ Sales for 7/2,6/25,6/18, & 6/11.

I want to create dynamic date measures that will compare the latest 4 weeks, 12 weeks and 24 weeks to these static measure values.

So when I get $ Sales data for week ending 9/17/21. I want to be able to click refresh and I can see the latest 4 weeks of $ sales data sum'd vs the L4 $ Sales prior to 7/09. 

Is there a way I can acheive this? I would like to be able to do this and click a button/filter etc and be able to look at all my data at the last 12 weeks or Last 4 weeks or Last 24 Weeks intervals.

I greatly appreciate any and all help! 

[Anonymous]

I appreciate any help! Sorry, I'm having some trouble working with the info you pasted. 

[amitchandak]

@Anonymous , You need that based on Today or selected date. Based on Today

This Week today =
var _st = today() +-1*WEEKDAY(today(),2)+1
var _end =today()+ 7-1*WEEKDAY(today(),2)
return
CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]>= _st && 'Date'[Date]<=_end )) //use all('Date') if need in filter

Last 4 Week today =
var _st = today() +-1*WEEKDAY(today(),2)+1 - 4*7
var _end =today()+ 7-1*WEEKDAY(today(),2)   // for till today only use Today()
return
CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]>= _st && 'Date'[Date]<=_end ))

//use all('Date') if need in filter

Last 8 Week today =
var _st = today() +-1*WEEKDAY(today(),2)+1 - 8*7 
var _end =today()+ 7-1*WEEKDAY(today(),2)// for till today only use Today() 
return
CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]>= _st && 'Date'[Date]<=_end ))

//use all('Date') if need in filter

For using selected date, Create a column in date table

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Last 4 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

Hi Armitchandak,

I appreciate the quick response. I am a little new at BI and was hoping you could elaborate a little please 🙂

On the weekly question, I only get data points at the weekly level so I really just need the latest data points, it doesn't matter what the current day is. Example: My last week is week ending 9/10/21. So for latest 4 I would just need 9/10. 9/3, 8/27, 8/20.

When I tried your formula for the created measure,  I get the below results....

What exactly should I plug in for the

Sales[Sales Amount]

&

'Date','Date'[Date]  ?

on the column creation I get the below greyed out. Any idea on what to plug in for that as well?

I appreciate the help once again! 

[Anonymous]

Just for context, this is my date hierarchy and my table name is "sales consumption". I only have the one table, no other date table etc.