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 table.  Can anyone help?

 

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

  • 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