Calculated Column with Current Row Values and Previous Dates in Power BI (DAX)

Question

I'm trying to get a calculated column (not a measure) that gets the sum of a column based on the values in the current row of that table for dates that are 1 month lagged to the date on the current row. My table has dates that are the 1st day of every month only .. no other days in the month. I'm asking the question about DAX; however, I have no problem implementing in M Language in Power Query (actually would probably prefer) if there is a solution that way as well.

I have been able to get a measure to work using something like this..

CALCULATE(SUM(AMT), DATEADD(DATECOLUMN, -1, MONTH))

But I'd like to be a new column instead.

Assuming the table looks something like this..

A    B        C          D     AMT
6    BAC456   5/1/2019   TEST  25
2    EPS123   4/1/2019   TEST  45
2    EPS123   3/1/2019   TEST  65
6    BAC456   4/1/2019   TEST  43
6    BAC456   4/1/2019   TEST  88
7    GRE123   4/1/2019   TEST  90
9    BAC456   4/1/2019   TEST  43

I'd like to have another column in this table where the first row would be:

A    B         C          D      AMT   NEWCOL
6    BAC456    5/1/2019   TEST   25    131

Second row would be:

A    B         C          D      AMT   NEWCOL
2    EPS123    4/1/2019   TEST   45    65

etc..

In cases where the month column is the first month in the entire table NEWCOL would be 0

Answer 1

To get 131, I'm assuming that you are requiring a match on both columns A and B.

NewCol =
CALCULATE (
    SUM ( Table1[AMT] ),
    ALLEXCEPT ( Table1, Table1[A], Table1[B] ),
    PREVIOUSMONTH ( Table1[C] )
)

This sums the column AMT keeping the row context of columns A and B and specifies the previous month as a filter on C. Note that this returns a blank for rows that don't have a previous month. If you'd prefer 0 then add + 0 after the last closing parenthesis.

---

If PREVIOUSMONTH doesn't work, then try this:

NewCol =
CALCULATE (
    SUM ( Table1[AMT] ),
    ALLEXCEPT ( Table1, Table1[A], Table1[B] ),
    Table1[C] = EOMONTH ( EARLIER( Table1[C] ), -2 ) + 1
)

For date = 5/1/2019, EOMONTH ( date, -2 ) returns 3/31/2019. Add one day to get 4/1/2019.

Answer 2

To achieve this easily in a calculated column, you need something like this before writing the final DAX.

Month Num = MONTH(MyTable[C])
Month Diff = DATEDIFF(MyTable[C],MAX(MyTable[C]),MONTH)

And now you have these Month differences and Month numbers defined, you can write a DAX like this -

Amount - New Column = 
Var selectedValue_B = MyTable[B]
Var SelectedValue_MonthDiff = MyTable[Month Diff]
Var out1 = CALCULATE(SUM(MyTable[AMT]), FILTER(ALL(MyTable), MyTable[Month Diff] = SelectedValue_MonthDiff+1 && MyTable[B] = selectedValue_B)) + 0
return out1

This makes my table to look something like,

I have used Var(Variables) in my formula to help you understand what is happening inside the formula.

Kindly accept the answer if it solves your problem.