Annualized EPS

[baronraghu]

HI All,

I have two tables

Table 1- Stock Price has daily stock price for last two years

01-12-2018   100

02-12-2018   98

03-12-2018   102

04-12-2018   105

05-12-2018   96

.

.

.

.

01-03-2018   150

02-03-2018   158

03-03-2018   142

04-03-2018   156

05-03-2018   147

Similarly I have another table with quarterly Earning per Share (EPS)

31-12-2016    06

31-03-2017    05

30-06-2017    09

30-09-2017    12

31-12-2017    16

31-03-2018    04

30-06-2018    15

30-09-2018    10

31-12-2018    12

31-03-2019    18

and so on

Now I want two have a single table which shows the price and annual EPS like below

Date               Price       Annual EPS

01-12-2018     100        (10+15+4+16)= 45

02-12-2018     98        (10+15+4+16)= 45

03-12-2018     102        (10+15+4+16)= 45

.

.

.

.

.

01-03-2018   150   (12+10+15+4)= 41

02-03-2018   158   (12+10+15+4)= 41

03-03-2018   142   (12+10+15+4)= 41

04-03-2018   156   (12+10+15+4)= 41

05-03-2018   147   (12+10+15+4)= 41

Thanks

[AlB]

@baronraghu 

I'd need to see the pbix. Can you share it?

Also, what is the logic to choose the four quarters of EPS for a specific date?

I guess we have to take into account the name of the stock, although you didn't mention it earlier.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @baronraghu 

Create a calculated column in  your Prices table:

New col =
CALCULATE (
    SUM ( EPSTable[EPS] ),
    FILTER (
        ALL ( EPSTable[Date] ),
        YEAR ( EPSTable[Date] ) = YEAR ( PricesTable[Date] )
    )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[baronraghu]

@AlB 

I tried your suggestion,

1. The output that  I am getting is the EPS for only quarter ending dates and not for the remianing days.
2. This is also the EPS of that quarter and not TTM EPS.

This is the output screeen

 I have calculted the annualized EPS in the EPS table as below

In the Price table i have mapped quarter ending for each date and now trying to match the annualized eps for each entry.

 This way I am tring to calculte the PE ratio for each day trded price- Stock Price/ Annualized EPS