Weighted average prior month Dax Calculation

DAX DAX Calculations
1

My model is made up of month end actual balance sheet balance and associated interest rates and I’m looking to compare month on month change in both balances & weighted rates (earnings). The data is organised at account level aggregating up into different summarised levels.

I have created the measure ‘Current Weighted Rate’ for the current month as follows which works ok:-

CM Weighted Rate =
DIVIDE(
SUMX('Margin&BalanceSheet','Margin&BalanceSheet'[interestbalance]*'Margin&BalanceSheet'[interestrate]),'Key Measures - Current Month'[CM Interest Balance],0)

I’m now trying to create the same measure for the previous month as follows but I get an error.  

LM Weighted Rate =

DIVIDE(
CALCULATE(SUMX('Margin&BalanceSheet'[interestbalance]), DATEADD('Margin&BalanceSheet'[interestrate],-1,MONTH)) *
DATEADD('Margin&BalanceSheet'[interestrate],-1,MONTH),

CALCULATE(SUMX('Margin&BalanceSheet'[interestbalance]), DATEADD('Margin&BalanceSheet'[asatdate],-1,MONTH)))

Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2.

Other expressions for previous month balances work fine

LM Interest Balance =
CALCULATE(SUM('Margin&BalanceSheet'[interestbalance]), DATEADD('Margin&BalanceSheet'[asatdate],-1,MONTH))

Any suggestions would be appreciated.

2

Hello @Simon_Garrett .

I did a test with my measurements and it is working as you can see in the image below.

image
image937×382 12.3 KB

The measures I used in the test are:

  1. Opportunity = DISTINCTCOUNT(‘Pipeline Stage Data’[History Sort Key])
  2. Divide Opportunity = DIVIDE([Opportunity], 2 , 0)
  3. Divide Opportunity LM = CALCULATE ( [Divide Opportunity],PREVIOUSMONTH(Dates[Date]))

So just implement this code.

CM Weighted Rate =

Var A = SUMX('Margin&BalanceSheet',
		'Margin&BalanceSheet'[interestbalance]*'Margin&BalanceSheet'[interestrate])

Var B = 'Key Measures - Current Month'[CM Interest Balance]

Return
	DIVIDE( A,B,0)

----------------------------------

LM Weighted Rate = 
	CALCULATE([CM Weighted Rate],
		PREVIOUSMONTH(Dates[Date]))

Any suggestions would be appreciated.

Best regards
Gifted

3

Thanks for the reply, but i’m not sure this will work for me.
I think that I have another complexity when calculating the weighted rate for the previous month as it has two variables which both need to reference the previous month.

In essence my weighted rate is calculated using sumx by mutiplying the [interestbalance] by the [interestrate] on all my product lines and then dividing it by the sum of the[interestbalance] to give me my current months weighted rate as below and that works fine.
CM Weighted Rate =
DIVIDE(
SUMX(‘Margin&BalanceSheet’,‘Margin&BalanceSheet’[interestbalance]*‘Margin&BalanceSheet’[interestrate]),
‘Key Measures - Current Month’[CM Interest Balance],0)

The complexity is that my prior month weighted calculation needs to be based on the balance for last month multiplied by the rate for the last month. Getting the balance for last month is no problem using the adddate function, but the issue i have is bringing the rate into the same calculation as its not a SUM of rate.
I think that i have a less then elegant solution by adding a new column to the data model which multiplies the balance by the rate for all products and then my new measure can reference this for the prior month and divide by the prior month total.

The attached excel will i hope make it a little clearer of the measures i’m looking for

weighted rate.xlsx (15.8 KB)

4

Hi @Simon_Garrett, due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.