Calculating monthly total

[Anonymous]

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

[Anonymous · Accepted 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. 

[v-henryk-mstf · Accepted solution]

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:

Here is the sample pbix file.

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.

[Anonymous]

Works like a charm - thank you! 

[PaulDBrown]

@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

[Anonymous]

Thanks for the soluton, @PaulDBrown ! I tried the below solution first, as it was more simple and it worked but thanks for your contribution

[Anonymous · Accepted 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.