Forum Discussion

jaybears130's avatar
jaybears130
Frequent Visitor
5 years ago
Solved

Calculated Column Showing Previous Month's Value

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 ta...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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