Prior month totals as calculated column

[leolapa_br]

I have the following code...

Tbl = 
SUMMARIZECOLUMNS( 
	dDates[Date], 
	"Invested amount", [Purchase total] 
)

... that produces a calculated table whose screenshot follows:

What I need is a calculated column that shows on each row the prior month's total of [Invested amount] respective to each row.

The code below manages to calculate the month's total for the month that relates to each row, not the prior month, so it doesn't work for me...

Inv. amount, monthly totals = 
VAR Date_Ref = Tbl[Date]
RETURN
	CALCULATE( 
		SUM( Tbl[Invested amount] ), 
		FILTER( 
			Tbl, 
			MONTH( Tbl[Date] ) = MONTH( Date_Ref ) && 
			YEAR( Tbl[Date] ) = YEAR( Date_Ref ) 
		) 
	)

... as it can be shown by the screenshot below (I manually added an extra "column" in red to help illustrate exactly what I really need):

The measure that follows does the required job, but the problem is that it's a measure, and what I really need is a calculated column. Can someone please help?

Inv. amount, monthly totals PRIOR month  = 
CALCULATE( 
	SUM( Tbl[Invested amount] ), 
	PREVIOUSMONTH( Tbl[Date] ) 
)

Thanks!

[leolapa_br · Accepted solution]

The following code did it for me:

SUMX( 
	FILTER( 
		ALLSELECTED( Tbl ), 
		FORMAT( Tbl[Date], "YYYYMM" ) = FORMAT( EDATE( EARLIER( Tbl[Date] ), -1 ), "YYYYMM" ) 
	), 
	Tbl[Invested amount] 
) 

[leolapa_br · Accepted solution]

The following code did it for me:

SUMX( 
	FILTER( 
		ALLSELECTED( Tbl ), 
		FORMAT( Tbl[Date], "YYYYMM" ) = FORMAT( EDATE( EARLIER( Tbl[Date] ), -1 ), "YYYYMM" ) 
	), 
	Tbl[Invested amount] 
) 

[tamerj1 · Accepted solution]

@leolapa_br 

That won't work for January month showing December values. 
yes there was a typo in my code. Sorry I'm typing on the phone. Please try

Inv. amount, monthly totals =
VAR CurrentYYMM =
FORMAT ( Tbl[Date], "YYMM" )
RETURN
SUMX (
TOPN (

1,
FILTER (
ADDCOLUMNS ( Tbl, "@YYMM", FORMAT ( Tbl[Date], "YYMM" ) ),
[@YYMM] < CurrentYYMM
),
[@YYMM]
),
Tbl[Invested amount]
)

[leolapa_br]

Hi @tamerj1 since you mentioned my code wouldn't work with December values in January, I checked it and somehow it also did work on such occasions.

But since your code also works I'm going with yours just to be on the safe side.

Thanks!

[tamerj1]

@leolapa_br 

Yes you are right. I didn't notice the EDATE function. Your solution is even better. Good job 👍 

[tamerj1]

Hi @leolapa_br 

please try

Inv. amount, monthly totals =
VAR CurrentYYMM =
FORMAT ( Tbl[Date], "YYMM" )
RETURN
SUMX (
TOPN (
FILTER (
ADDCOLUMNS ( Tbl, "@YYMM", FORMAT ( Tbl[Date], "YYMM" ) ),
[@YYMM] < CurrentYYMM
),
[@YYMM]
),
Tbl[Invested amount]
)

[leolapa_br]

@tamerj1 thanks a lot for lending a hand on this one...

Your code returns the following error: Column '@YYMM' cannot be found or may not be used in this expression.

Apparently DAX doesn't accept the argument [@YYMM] as a table for the TOPN function.

[leolapa_br]

I also tried using 'Tbl' instead of [@YYMM] as the table argument under TOPN but I still get an error, this time: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.