Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Value from last week

Hello all,

 

I'm trying to collect whatever the value is from last week within a custom column. Below is an example of what it would look like

 

 

I've started the DAX code but currently this only turns blank fields: 
VAR most_recent_date = LASTDATE('data'[date]) -7


VAR Calc = CALCULATE(
SELECTEDVALUE('data'[rollingRate]),
FILTER(ALL('data'), FORMAT('data'[date], "dd/mm/yy") = FORMAT(most_recent_date, "dd/mm/yy") ))

RETURN

Calc


Thanks in advance for any help!!
 
dax 
  • lbendlin's avatar
    lbendlin
    4 years ago

    Remember that you have to calculate this from the point of view of the visual element if you do this as a measure.

     

    So instead of 

    VAR most_recent_date = LASTDATE('data'[date]) -7

     

    you would say

    VAR most_recent_date =DATEADD('data'[date]), -7,DAY)

     

    Note that "the value from last week"  is not the same as "the value from the day seven days before the current day"

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Remember that you have to calculate this from the point of view of the visual element if you do this as a measure.

     

    So instead of 

    VAR most_recent_date = LASTDATE('data'[date]) -7

     

    you would say

    VAR most_recent_date =DATEADD('data'[date]), -7,DAY)

     

    Note that "the value from last week"  is not the same as "the value from the day seven days before the current day"

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Anonymous 

     

    Please see if the following query.

     

    Prev Week Sales =
    LOOKUPVALUE('Table'[Sale Value],'Table'[Date],DATEADD('Table'[Date],7,DAY))
     
    Hope this helps.
    Kindly accept it as a solution if it satisfies your requirement.