Receiable debt for particular date

[Anonymous]

Hello,

I have some struggle with DAX.

I want to calculate Total DEBT for receivables for particular date. Receivable becomes a debt when Over days is more than 0. For this I calculated two measures:

Over days - calculates for how many days the debt is

Over days = 
VAR datediff=DATEDIFF(FIRSTDATE('Customer Ledger Entry'[Due date]); LASTDATE('Calendar'[Date]);DAY)
RETURN
IF(datediff>=0 ; datediff ;0)

Balance  - calculates the sum of the receivable sums. It's a sum of all operations.

Everything is ok, when I'm calculating for every document, I just put in Visual filter more than 0.

The problem is that when I try to make Debt visualization according to month. It bring  Over days for a month not for the

particular document. So I tried to calculate a measure for debt receivable

Debt receivable = SUMX(FILTER(VALUES('Customer Ledger Entry'[Document No.]);[Over days]>0);[Balance, EUR])

but it remain the same: everything ok when it shown by document, and wrong when calculated by month:

Could you help me to write correct DAX for calculating Balance amount where over days ar more than 0?

Pbix file for test:

https://www.dropbox.com/s/h425n4qf91c236i/Receivable%20debt.pbix?dl=0

[v-yuta-msft]

@Anonymous ,

So your requirement is to achieve aggregation value based on same document No, right? If it is, to be general, I would suggest you to convert [Balance, EUR] and [Over days] to calculate columns because measure nested in another measure may cause error, then you may modify measure Debt Receivable as pattern below:

Debt receivable = CALCULATE([Balance, EUR], FILTER(ALLEXCEPT('Customer Ledger Entry', 'Customer Ledger Entry'[Document No.]),[Over days]>0))

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks, @v-yuta-msft for the replay,

Yes, I need aggregation value based on documents.

I tried to do so, but the problem is with Over days Measure - it's dynamical and depends on date filter in the page, so I can't calculate it as a column.