SUMMARIZE functions for new table

[pbbasic]

Hello,

I am trying to create new table from the existing table based on the date selected. but the results are not as expected from the DAX formulas.

current table

Instrument	Transaction	Date	Amount
A	Buy	1-Jun-10	-25
A	Buy	5-Jun-10	-75
A	Sell	19-Jun-10	30
A	MonthEnd	30-Jun-10	60
A	Buy	1-Jul-10	-125
A	Buy	5-Jul-10	-85
A	Sell	19-Jul-10	300
A	MonthEnd	30-Jul-10	400
C	Buy	10-Jun-10	-89
C	Sell	20-Jun-10	45
C	MonthEnd	30-Jun-10	120
C	Buy	8-Jul-10	-75
C	Sell	22-Jul-10	30
C	MonthEnd	30-Jul-10	60

if the user selected date is 30-jul-10   only july "month end" data should be considered along with other transaction.

Expected table :

Instrument	Transaction	Date	Amount
A	Buy	1-Jun-10	-25
A	Buy	5-Jun-10	-75
A	Sell	19-Jun-10	30
A	Buy	1-Jul-10	-125
A	Buy	5-Jul-10	-85
A	Sell	19-Jul-10	300
A	MonthEnd	30-Jul-10	400
C	Buy	10-Jun-10	-89
C	Sell	20-Jun-10	45
C	Buy	8-Jul-10	-75
C	Sell	22-Jul-10	30
C	MonthEnd	30-Jul-10	60

tried with SUMMARIZE functions

Can someone help me on this?

Thanks

[lbendlin]

Following this example

https://community.powerbi.com/t5/Desktop/How-to-find-latest-date-value-in-a-group/td-p/379104

here's a sample measure that you can add to your table and use for filtering.

Include = 
var latest = CALCULATE(max(Table[Date                  ])
    ,Table[ Transaction       ]="MonthEnd"
    ,ALLEXCEPT(Table,Table[Instrument       ]))
return SWITCH(TRUE(),MAX(Table[ Transaction       ])<>"MonthEnd",1
    ,max(Table[Date                  ])=latest,1
    ,0)

[lbendlin]

here's a slightly more robust implementation that handles date filters better (for example if you chose to exclude July 30 for whatever reason)

Include = 
var i = max(Table3[Instrument       ])
var a = ALLSELECTED(Table3)
var latest = CALCULATE(max(Table3[Date                  ])
    ,Table3[ Transaction       ]="MonthEnd"
    ,Table3[Instrument       ]=i
    ,a)
var s = SWITCH(TRUE(),MAX(Table3[ Transaction       ])<>"MonthEnd",1
    ,max(Table3[Date                  ])=latest,1
    ,0)
return s