Forum Discussion
Need help displaying values before/after a specific date
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!
4 Replies
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 filterLast 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=pnAesWxYgJ8Hi 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!
