Compound results up depending on date hierarchy

[FrankProperty]

Hi all,

I have a dashboard where the user selects a combination of assets (a list of several hundred options) and the returns over time are shown on a line chart. The user can then drill down across a date hierarchy to see annual, quarterly and monthly returns.

The data for the returns calculation is on a monthly basis and the returns are calculated from the underlying data components (ie. numerator and denominator are measures which are calculated depending on the selection and time period) for accuracy rather than a weighted average of the returns.

Ideally I want Power BI to calculate the monthly return and then show higher hierarchy levels as a compound of the monthly. The steps would therefore be as follows:

1. User selects which assets they want using a slicer
2. Power BI calculates the monthly returns
3. Power BI then calculates the compounded returns to display at quarterly/annual level.

My first thought was to put the calculated monthly returns into a table and then compound and display those results in the chart but I don't think the tables are that clever in Power BI (I could be mistaken). Is there a clever way to do the above in DAX?

Dataset is sensitive but I can whip together a really simple example if it helps.

[FrankProperty]

It is similar but unfortunately not the same as the IRR.

I've had a look at the maths, simplified the expression and created a DAX expression which calculates the returns for each of the 12 previous months and then multiplies them together to give the annual compound return for that month. It isn't pretty nor quick but it gives me the answer I am looking for (at a cost of 500ms per calculation!).

DAX as follows:

Total Property Return =

//Calculate each month's TPR

VAR TPR0 = CALCULATE((SUM('Forecast returns FINAL'[Net Value END])+SUM('Forecast returns FINAL'[ Net Operating Income]))/(SUM('Forecast returns FINAL'[Net Value START])-SUM('Forecast returns FINAL'[ Total Leasing & Capital Costs (in period)])))

VAR TPR1 = CALCULATE((SUM('Forecast returns FINAL'[Net Value END])+SUM('Forecast returns FINAL'[ Net Operating Income]))/(SUM('Forecast returns FINAL'[Net Value START])-SUM('Forecast returns FINAL'[ Total Leasing & Capital Costs (in period)])),DATEADD(Dates[Date],-1,MONTH))

....The above TPR1 expression is repeated all the way up to TPR11 for each month with -1 added to each month

//Calculate compounded annual TPR

VAR TPR = ( TPR0 * TPR1 * TPR2 * TPR3 * TPR4 * TPR5 * TPR6 * TPR7 * TPR8 * TPR9 * TPR10 * TPR11 ) -1

Return

//Eliminate results in first year where less than 12 months and on days between data periods

IF(TPR=-1,"",TPR)

I'm now interested in optimising this query. If the individual TPR queries could be improved then there is a x12 saving to be made! Any thoughts? 

[Anonymous]

This is a very naïve way of calculation. Instead of this, you should use the GENERATE function or an iteration over a suitable chosen column in a table. Probably over months.

[FrankProperty]

I agree it is however I have fairly limited knowledge of the back end calculations. I've looked at the GENERATE function but not sure how it would work in this instance. Are you able to help?

I've tackled the problem in another way by creating an index (in a benchmark context rather than a power query context). The code provides greater flexibility for my analysis however there are huge inefficiencies and it increases the time to calculate from 500ms to 3.5s.

The new code has two measures. The first multiplies all the % returns from the previous periods together to show an index value as at that date. The second then compares the index value today against a year ago. The problem with the TPR Index measure is that it ideally should take the index value in the last period and multiply that by the return in this current period. However I think it is calculating the returns for each period when calculating the index at each period (turning a few hundred calculations into many thousands). Is there a more efficient way using GENERATE? 

TPR Index =

//Filter dates for everything before and including the current period

VAR _AllPreviousPeriods = FILTER(ALL('Dates'),'Dates'[Date] <= MAX('Dates'[Date]))

RETURN

//Return the product of the Returns for all those dates - this creates the index benchmark

PRODUCTX( _AllPreviousPeriods, [TPR] )*100

TPRinYear =

//Takes the index today and compares against the index 1 year ago

[TPR Index]/CALCULATE([TPR Index],SAMEPERIODLASTYEAR(Dates[Date]))-1

[Anonymous]

For your TPR calculation use the function GENERATESERIES( 0, 11, 1 ). This will produce the values ([Value] field) that you'll then stick into DATEADD( Dates[Date], -[Value], MONTH ). By doing this you'll get rid of the manual repetitions.

Also, you should never, ever do things like this:

F(TPR=-1,"",TPR)

Measures should return only one data type (and BLANK if needed). The last bit should be IF( TPR <> -1, TPR ).

[FrankProperty]

Thanks Daxer. That tidies up the original code but I am still not satisfied that it is the best way of calculating.

I looked into GENERATE using the below code. The line graph shows the return for each period so I think the generate function is working. However, it doesn't seem to be considering previous periods in the PRODUCTX calculation so it isn't creating the index I want. I tried using the ALL function (as I did in my previous code) but it cannot be used for a table expression. Any thoughts?

TPR 2 =

//Define the dates for calculation and create a table which has the return for each period. Intention is to use this table for the subsequent calculations.

VAR BaseCalendar =

CALENDAR ( MIN('Forecast returns FINAL'[Date]), max('Forecast returns FINAL'[Date]))

VAR TPRtable =

GENERATEALL (

BaseCalendar,

VAR TPRinPeriod = SUM('Forecast returns FINAL'[Numerator])/sum('Forecast returns FINAL'[Denominator])

RETURN ROW (

"TPRinPeriod", TPRinPeriod

)

)

RETURN

//Filter the previously generated table to show all dates up to that point and then multiply the return in each period to create the index benchmark. 

CALCULATE (

PRODUCTX ( TPRtable , [TPRinPeriod] * 100) ,

FILTER ( TPRtable , [Date] < MAX([Date])

)

)

[FrankProperty]

Light blue shows the correct index (stepped) result using my earlier method. The dark blue shows the latest code which is just returning the TPR in that period (rather than compounding up).

[Anonymous · Accepted solution]

// hidden measures:
[_NetValueEnd] =
	SUM( 'Forecast returns FINAL'[Net Value END] )
[_NetOperatingIncome] =
	SUM( 'Forecast returns FINAL'[ Net Operating Income] )
[_NetValueStart] =
	SUM( 'Forecast returns FINAL'[Net Value START] )
[_TotalLeasingCapitalCost] =
	SUM( 'Forecast returns FINAL'[ Total Leasing & Capital Costs (in period)] )


[TPR] =
var __rawFormula =
	PRODUCTX(
	
		GENERATESERIES( 0, 11, 1 ),
		
		var __monthsBack = [Value]
		RETURN
		CALCULATE(
		    DIVIDE(
		        [_NetValueEnd] + [_NetOperatingIncome],
		    	[_NetValueStart] - [_TotalLeasingCapitalCost]
		    ),
		    DATEADD(
		    	// Dates must be marked as the Date table
		        Dates[Date],
		        -(__monthsBack),
		        MONTH
		    )
		)
	)
var __finalFormula = __rawFormula - 1
return
	IF( __finalFormula <> -1, __finalFormula )

[FrankProperty]

Thanks @Anonymous - that code seems to half the computation time and is much better!

I've accepted your last post as the solution. Thanks for your help getting here (even if it felt like banging your head against the wall at times!).

[Anonymous]

Hi there. Sadly, due to my company's policy I can't even view the file you attached. Could you please paste some screenshots for me to see what it is you want? When you say "returns", do you mean the IRR - the internal rate of return?

[FrankProperty]

Here is an example dataset with workings:

Dataset