Calculated column to compare current running total column to last activity date in previous month

[lsealy]

Thank you for any help. Trying to get a calculated column 'Monthly_Change' that, for each row, subtracts the RT value on the last ActionDate in the previous month from the current RT value. Tried this, didn't work:

MonthlyChange = crf38_fieldactions[RT] - CALCULATE(SUM(crf38_fieldactions[RT]), LASTDATE(PREVIOUSMONTH(crf38_fieldactions[ActionDate])))

[FreemanZ · Accepted solution]

hi @lsealy ,

not sure if i fully get you, supposing you have a data table like:

date	RT
9/1/2023	4
9/9/2023	4
9/17/2023	1
9/25/2023	2
10/3/2023	3
10/11/2023	2
10/19/2023	2
10/27/2023	6
11/4/2023	4

try to add a calculated column like:

column = 
VAR _prt=
MAXX(
    TOPN(
        1,
        FILTER(data, data[date]<=EOMONTH(EARLIER(data[date]), -1)),
        data[date]
    ),
    data[RT]
)
RETURN  
    IF(
        NOT ISBLANK(_prt), 
        [RT] - _prt
    )

it worked like: