Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 m...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.