Monthly difference from YTD trial balance

[nileshp]

I have a table with monthly trial balances for every month end dates and want to make derive monthly MIS. Financial year ends 31st March

TB table
Month end date, ledger, YTD amt
30-04-2016, sales, 300
30-04-2016, exps, -100
...
31-05-2016, sales, 450
31-05-2016, exps, -200
...


Mis report for May 2016
Sales 150
Exps -100

Amt starts from zero from April 1.
Mis may be required for different periods like quarter, year, etc.

Tried with calculate, filter but no success

Thanks in advance

Nilesh P

 

[nileshp]

I have uploaded some test data with powerpivot. Hopefully it helps.

https://www.dropbox.com/s/af2dmsfl3l6q0h2/YTD TestData.xlsx?dl=0

Nilesh