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 the previous month.

 

The dates are all the first date in the month. The example of what i would like to return is below

 

I've tried all sorts of code, none of which i can get to work!

 

Any help is greatly appreciated

 

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])

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , Create a new column

    Previous value =

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

     

    Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

    • 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
        Icon for Super User rankSuper 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])

  • latimeria's avatar
    latimeria
    Icon for Solution Specialist rankSolution Specialist
    3 years ago

    Anonymous ,

    If this can help

    Previous value = 
VAR CurrentID = 'Previous Row DAX'[ID]
VAR PreviousMonth_1 =
    PREVIOUSMONTH ( 'Previous Row DAX'[Date] )
VAR PreviousValue =
    CALCULATE (
        MAX ( 'Previous Row DAX'[Current value] ),
        REMOVEFILTERS ('Previous Row DAX'),
        CurrentID = 'Previous Row DAX'[ID],
        PreviousMonth_1 = 'Previous Row DAX'[Date]
    )
RETURN
    PreviousValue