Solved: Compound results up depending on date hierarchy - Page 2

FrankProperty · ‎07-27-2020

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:

User selects which assets they want using a slicer
Power BI calculates the monthly returns
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.

Anonymous · ‎07-31-2020

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

View solution in original post

Compound results up depending on date hierarchy

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024