Calculated Column Showing Previous Month's Value

[jaybears130]

I have a table with price indexes for a list of SeriesID's dated for the end of every month.  I want to add a column that will show me the previous month's index for each particular SeriesID in my table.  Can anyone help?

[Anonymous · Accepted solution]

Hi @jaybears130 ,

You can create a calculated column as below:

Previous Month's Value = 
VAR _curmonthnum = 'Table'[Month Number]
VAR _curyear = 'Table'[Year]
VAR _year =
    IF ( _curmonthnum = 1, _curyear - 1, _curyear )
VAR _monthnum =
    IF ( _curmonthnum = 1, 12, _curmonthnum - 1 )
RETURN
    CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[seriesID] = EARLIER ( 'Table'[seriesID] )
                && 'Table'[Year] = _year
                && 'Table'[Month Number] = _monthnum
        )
    )

Best Regards

[amitchandak]

@jaybears130 , I am not seeing index. I am assuming you need value

New column

Last Month Value =
var _1 = eomonth([Date] ,-1)

var _3 = [SeriesID]
return
sumx(filter(Table, eomonth([Date] ,0) = _1 && [SeriesID] =_3), [Value])