Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Return previous month value based on ID

Hi   This is driving me insane!   I have a table full of ID's with values for particular dates. I would like to create a new column which returns the previous value for each ID, but only if it is...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anonymous , My mistake , it should me maxx

     

    Previous value =

    var _min = maxx(filter(Table,[ID] = earlier([ID]) && [Date] < earlier([Date]) ),[Date])
    return
    maxx(filter(Table,[ID] = earlier([ID]) && [Date] =_min),[Current Value])

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks but this doesn't work i'm afraid. It returns the value if it has changed over the time period and not what it was the previous month. See below

 

amitchandak's avatar
amitchandak
Super User
3 years ago

Anonymous , My mistake , it should me maxx

 

Previous value =

var _min = maxx(filter(Table,[ID] = earlier([ID]) && [Date] < earlier([Date]) ),[Date])
return
maxx(filter(Table,[ID] = earlier([ID]) && [Date] =_min),[Current Value])

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    So unfortunately this still doesn't return the desired result.

     

    It's finding the previous instance regardless of the date. I only want it to look at the previous month. So for September it should be showing blank and not 1 as there is no record of the ID in August