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.
Hi
I wish to create a table, which shows dividend payouts for company for each month and year.
I have a table1 that shows dividends for each month updating through an API.
StockTicker Date Dividendamount
PEP January 2021 1
PEP December2020 1
PEP November 2020 0
PEP September 2020 0,5
GILD December 2020 2
GILD November 2020 2
GILD January 2021 2
I also have a table2 to keep track of owned dividend stock and when I bought them.
StockTicker DayOfPurchase Shares
PEP November 2020 5
PEP December 2020 10
GILD January 2021 10
My total of January are now 15 shares of PEP and 10 shares of GILD.
My result should be a table showing my dividend payout from month to month:
November 2020: 0 (5 shares of PEP but no payout for PEP in November 2020)
December 2020: 15 (15 shares of PEP = 15 and 0 shares of GILD)
January 2021: 35 (15 shares of PEP = 15 and 10 shares of GILD = 20)
Thanks a lot!
Mikkel
Solved! Go to Solution.
Using Power Query you could merge the two table using both StockTicker and Date as prerequisites (hold ctrl to select more than 1). This would result in an additional column showing your number of shares, then it's just a simple calculation to get your payout.
Hi @Anonymous ,
According to the test data and requirements you provided, I did the following test: First, use merge in Power Query to get the following table.
Create a measure to get the Amount value of the latest date for different StockTickers:
Finally, the sum is as follows:
If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to the test data and requirements you provided, I did the following test: First, use merge in Power Query to get the following table.
Create a measure to get the Amount value of the latest date for different StockTickers:
Finally, the sum is as follows:
If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Works like a charm - thank you!
@Anonymous
Here is one way:
Firstly I created Dimension tables for both the periods and the StockTicker. The model looks like this:
Next create the measures:
Sum of Dividend = SUM(DividendTable[DividendAmount])
Sum of Shares = SUM(Stock[Shares])
Cumulative Shares by Period = CALCULATE([Sum of Shares],
FILTER(ALL('Period Table'),
'Period Table'[Date] <= MAX('Period Table'[Date])))
Cumulative Shares by Stock = SUMX(DividendTable, [Cumulative Shares by Period])
and finally the measure to be used in the visual:
Payout (Shares) = SUMX(
SUMMARIZE(DividendTable,
'Period Table'[MonthYear], 'Dim StockTicker'[StockTicker],
"payout", [Sum of Dividend] * [Cumulative Shares by Stock]), [payout])
Create your visual using the fields from the Dimension Tables (StockTicker and Period) and the measures, and you get this:
I've attached a sample BIX file
Proud to be a Super User!
Paul on Linkedin.
Thanks for the soluton, @PaulDBrown ! I tried the below solution first, as it was more simple and it worked but thanks for your contribution
Using Power Query you could merge the two table using both StockTicker and Date as prerequisites (hold ctrl to select more than 1). This would result in an additional column showing your number of shares, then it's just a simple calculation to get your payout.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |