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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
mrothschild
Continued Contributor
Continued Contributor

Weighted Average for time constrained data

Excel file is: https://www.dropbox.com/s/93c2pfyzi03w1el/Income%20Yield.xlsx?dl=0

 

Sample data is below,  There's a related table that associated the "Asset ID" with "Project"

 

Some of these Asset ID's will have an Income Yield in the future and some will be in the present.  For example, if the Asset is scheduled to be purchased on 6/30/19 it will be in the future, but if the Asset was purchased on 10/31/18, it's in the past. 

 

I'm trying to get a weighted average (by the Equity Deployed) of the Current income yield if the Asset has already been purchased, or the first period future income yield if the asset is going to be acquired in the future.  In drafting this post, I realized that I could create a new calculated column called "Current Month Income Yield" which returns current month if asset purchase was in the past, and first future month if asset purchase is in the future, and then sumproduct that way, but I'm not sure how to create that calculated column.  

 

 Purchase DateEquity DeployedCurrent Month Income YieldFirst Month Income Yield
A10/31/201852.50%1.75%
B2/28/2019101.60%1.50%
C6/30/201972.00%2.00%

 

Desired output would be: 1.93% and in Excel is calculated: =SUMPRODUCT(Table2[Equity Deployed],Table2[Current Month Income Yield])/SUM(Table2[Equity Deployed])

 

 

 

 

 

 

 

 

 

NumberAsset IDCountCalendar MonthEquity DeployedIncome Yield - Base (monthly)

1EC135 (MSN 0467)03/31/2019$1,260,000 
2EC135 (MSN 0467)14/1/2019$1,260,0001.61%
3EC135 (MSN 0467)25/1/2019$1,260,0001.62%
4EC135 (MSN 0467)36/1/2019$1,260,0001.62%
5EC135 (MSN 0467)47/1/2019$1,260,0001.63%
6EC135 (MSN 0467)58/1/2019$1,260,0001.63%
7EC135 (MSN 0467)69/1/2019$1,260,0001.63%
8EC135 (MSN 0467)710/1/2019$1,260,0001.64%
9EC135 (MSN 0467)811/1/2019$1,260,0001.64%
10EC135 (MSN 0467)912/1/2019$1,260,0001.65%
11EC135 (MSN 0467)101/1/2020$1,260,0001.65%
12EC135 (MSN 0467)112/1/2020$1,260,0001.66%
13EC135 (MSN 0467)123/1/2020$1,260,0001.66%
14EC135 (MSN 0467)134/1/2020$1,260,0001.66%
15EC135 (MSN 0467)145/1/2020$1,260,0001.67%
16EC135 (MSN 0467)156/1/2020$1,260,0001.67%
17EC135 (MSN 0467)167/1/2020$1,260,0001.68%
18EC135 (MSN 0467)178/1/2020$1,260,0001.68%
19EC135 (MSN 0467)189/1/2020$1,260,0001.69%
20EC135 (MSN 0467)1910/1/2020$1,260,0001.69%
21EC135 (MSN 0467)2011/1/2020$1,260,0001.69%
22EC135 (MSN 0467)2112/1/2020$1,260,0001.70%
23EC135 (MSN 0467)221/1/2021$1,260,0001.70%
24EC135 (MSN 0467)232/1/2021$1,260,0001.71%
25EC135 (MSN 0467)243/1/2021$1,260,0001.71%
75EC135 (MSN 0472)03/31/2019$1,260,000 
76EC135 (MSN 0472)14/1/2019$1,260,0001.61%
77EC135 (MSN 0472)25/1/2019$1,260,0001.62%
78EC135 (MSN 0472)36/1/2019$1,260,0001.62%
79EC135 (MSN 0472)47/1/2019$1,260,0001.63%
80EC135 (MSN 0472)58/1/2019$1,260,0001.63%
81EC135 (MSN 0472)69/1/2019$1,260,0001.63%
82EC135 (MSN 0472)710/1/2019$1,260,0001.64%
2 REPLIES 2
v-yulgu-msft
Employee
Employee

Hi @mrothschild ,


  Purchase Date Equity Deployed Current Month Income Yield First Month Income Yield
A 10/31/2018 5 2.50% 1.75%
B 2/28/2019 10 1.60% 1.50%
C 6/30/2019 7 2.00% 2.00%

 

Desired output would be: 1.93% and in Excel is calculated: =SUMPRODUCT(Table2[Equity Deployed],Table2[Current Month Income Yield])/SUM(Table2[Equity Deployed])


Is above a known data table? Or should it be calculated based on Table1? Please provide detailed description about how to get the result of [Purchase Date] and [Equity Deployed], I could not find the relationship between these two tables.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

In my prior email, Table 2 was not related to Table 1 I was intending to use it as a simple example.  I have now updated Table 2 so it is related to Table 1 and looks as follows:

 

In this example, the desired output is 1.60%, from the formula: =SUMPRODUCT(Table2[Equity Deployed],Table2[Current Month Income Yield])/SUM(Table2[Equity Deployed])

 

ProjectAsset IDCalendar MonthCurrent MonthEquity DeployedAsset ID  - MonthCurrent Month Income YieldIncome Yield - Base (monthly)
AEC135 (MSN 0467)10/31/20183/1/201912.60EC135 (MSN 0467) - 03/01/191.63%1.61%
AEC135 (MSN 0472)10/31/20183/1/201912.60EC135 (MSN 0472) - 03/01/191.63%1.61%
AEC145 (MSN 9084)10/31/20183/1/201915.20EC145 (MSN 9084) - 03/01/191.62%1.60%
BH4 (MSN 4)2/28/20193/1/201915.28H4 (MSN 4) - 03/01/190.99%0.98%
BH5 (MSN 5)2/28/20193/1/201915.73H5 (MSN 5) - 03/01/191.25%1.23%
BH6 (MSN 6)2/28/20193/1/201915.90H6 (MSN 6) - 03/01/193.01%2.98%
CH7 (MSN 7)6/30/20193/1/201915.05H7 (MSN 7) - 06/30/190.99%0.99%
CH8 (MSN 😎6/30/20193/1/20196.18H8 (MSN 😎 - 06/30/191.26%1.26%
CH9 (MSN 9)6/30/20193/1/20195.80H9 (MSN 9) - 06/30/192.27%2.27%
DH10 (MSN 10)12/31/20193/1/20195.95H10 (MSN 10) - 12/31/191.47%1.47%

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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