Forum Discussion
Calculating monthly total
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.
Community ChampionAnonymous
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
Thanks for the soluton, PaulDBrown ! I tried the below solution first, as it was more simple and it worked but thanks for your contribution