Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
leolapa_br
Resolver I
Resolver I

Prior month totals as calculated column

I have the following code...

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

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

Tbl.png

 

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

Monthly totals.png

 

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!

2 ACCEPTED SOLUTIONS
leolapa_br
Resolver I
Resolver I

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

View solution in original post

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

View solution in original post

7 REPLIES 7
leolapa_br
Resolver I
Resolver I

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 

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

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!

@leolapa_br 

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

tamerj1
Super User
Super User

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

@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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors