Extract one month data from a quarterly table

[MikeH2]

I am trying to calculate the Dividend Yield for my IRA accounts.
I have a table of Transactions (buy, sell, dividends, etc).
I also have a table of End of Month Stock Values (broken out by account and Ticker).
I calculate the Dividend Yield by dividing the total dividend amount by the total end of month stock value (‘tis close enough).
My problem occurs when I switch my Pivot table from a monthly to a quarterly display.
My dividend amount stays the same (only 1 dividend/quarter), but the total end of month value roughly triples (it is summing the values for all three months).

My transaction table (Trans) looks something like this:

Date	Portfolio	Ticker	Category	Amt
1/1/2015	1	Abc	Buy	900
1/15/2015	1	Abc	_DivInc	10
1/15/2015	2	Abc	_DivInc	5

<tbody>
</tbody>

And the end of month values table (Val) looks something like this

Date	Portfolio	Ticker	Value
1/31/2015	1	Abc	1000
1/31/2015	2	Abc	500
2/28/2015	1	Abc	1100
2/28/2015	2	Abc	550

<tbody>
</tbody>

This measure pulls up the correct Value when I break it out by month – but not by quarter
=IF([DivMoEqValMo],SUM(Val[Value]),BLANK())

So I figured I needed to try a CALCULATE, FILTER combination like this:
=CALCULATE([Sum of Value],FILTER(Val,MONTH(Val[Date])=MONTH([DivDate])))
Where DivDate =CALCULATE(MAX(Trans[Date]),Trans[Category]="_DivInc")

But this just gives me blank values

Any suggestions?
Thanks
Mike

 

[Matt Allington]

Mike

Closing balance is a semi additive metric that needs to be handled differently to other metrics (otherwise you get this problem)

Read up about it here Semi-Additive Measures in DAX - SQLBI