Return previous month value based on ID

[Gav10]

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

[latimeria]

@Gav10 ,

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

[amitchandak]

@Gav10 , 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

[Gav10]

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 · Accepted solution]

@Gav10 , 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])

[Gav10]

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