dax - Filter PowerPivot based on multiple Date Criteria -

i trying apply time intelligence functions in powerpivot workbook concerning projects , money received them. have 3 relevant tables; matters, payments, , date table.

each matter has creationdate, , closuredate(from linked table). likewise, each payment has date. have reporting set decently, trying use time intelligence filter bit more clearly.

how can set powerpivot pivot table matters show existed within period selected. e.g. if select slicer 2014, don't want show matter created in 2015, or 1 closed in 2013. matter should have been active during period specified.

is possible?

you want show matters except creationdate after upper limit of date range looking @ or closuredate before lower limit of date range looking at.

assuming have data structure this, left-hand table matters , right-hand 1 payments:

if have calculated field called [total payments] adds payments in payments table, formula similar work:-

[payment in range]:=if(or(min(matters[creation date])>max('reporting dates'[date]),max(matters[closure date])<min('reporting dates'[date])),blank(),[total payments]) 

here result 1 month selected in timeline:

or 1 year selected in year slicer:

note: in example, have used disconnected date table.

also, see grand total adds payments because takes lowest of creation dates , highest of closure dates determine whether show total payment value. if important grand total shows correctly, additional measure required:

[fixed totals payment in range]:=if(countrows(values(matters[matter]))=1,[payment in range],sumx(values(matters[matter]),[payment in range])) 

replace [payment in range] in pivot table new measure , totals show correctly, however, work if matters[matter] used 1 of fields in pivot table.