cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MIchri
Resolver I
Resolver I

Calculating monthly total

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

2 ACCEPTED SOLUTIONS
Karlos
Super User I
Super User I

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. 

Karlos_0-1611517817457.png

Karlos_1-1611517932156.png

 

View solution in original post

v-henryk-mstf
Community Support
Community Support

Hi @MIchri ,


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.

v-henryk-mstf_0-1611654885480.png

Create a measure to get the Amount value of the latest date for different StockTickers:

v-henryk-mstf_1-1611654908635.jpeg


Finally, the sum is as follows:

v-henryk-mstf_2-1611654922947.png

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.

View solution in original post

5 REPLIES 5
v-henryk-mstf
Community Support
Community Support

Hi @MIchri ,


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.

v-henryk-mstf_0-1611654885480.png

Create a measure to get the Amount value of the latest date for different StockTickers:

v-henryk-mstf_1-1611654908635.jpeg


Finally, the sum is as follows:

v-henryk-mstf_2-1611654922947.png

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.

View solution in original post

Works like a charm - thank you! 

PaulDBrown
Super User II
Super User II

@MIchri 

Here is one way:

Firstly I created Dimension tables for both the periods and the StockTicker. The model looks like this:
Model.PNG

 

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:
result.PNG

 

 

I've attached a sample BIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

Karlos
Super User I
Super User I

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. 

Karlos_0-1611517817457.png

Karlos_1-1611517932156.png

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.