DAX and filter context - Running total of debtors

[Anonymous]

Stuck trying to work out what my debtor position is over a period of time.  For any order I can work out that on any date selected (from a disconnected calendar table) whether the order was a debtor.  This  works fine for each order but not for totals across 3 orders (see table below) - I understand this because I am not filtering per line and it calculating the debtor across all 3 orders for the entire time span.  If I filter over the calendar table or the order table I get the same result - it is fundamentally just wrong.  Need a nudge please.

Debtor Curr =

var startdate = min(Orders[DATE Closed]) //when the order becomes a debtor

var enddate = max(Orders[DATE Paid]) // when the order stops being a debtor

var mindate = min('Calendar Debtors'[Date]) // minimum date selected

var maxdate = max('Calendar Debtors'[Date]) // maximum date selected

return

CALCULATE(

sumx('Calendar Debtors',[Sale plus VAT Curr]+[Sage Non Stock Curr]),

filter('Calendar Debtors',

mindate>=startdate &&

maxdate<=enddate

)

)+0

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

measure =
var _max = maxx(ALLSELECTED('Calendar Debtors'),'Calendar Debtors'[Date])
var _min = minx(ALLSELECTED('Calendar Debtors'),'Calendar Debtors'[Date])
return
sumx('Orders',[Sale plus VAT Curr]+[Sage Non Stock Curr]),
filter('Orders',
_min>=min(Orders[DATE Closed]) &&
_max<=max(Orders[DATE Closed])
)
)+0