Overdebt receivables for particular date

[Anonymous]

Hello,

I'm struggling with this question for several weeks. Maybe someone has done that and will have some tips for me.

I want to calculate how many overdue receivables was on a particular date.

For this I have to evaluate:

1. Which of documents are overdue for that day
2. How much receivable is for those documents

I tried to calculate three measures:

How much days document are overdue:

PastDue = DATEDIFF(FIRSTDATE('Customer Ledger Entry'[Due date]); MAX('Calendar'[Date]);DAY)

Calculated column for how much receivable document has:

Receivable, EUR = CALCULATE(SUM('Detailed Customer Ledger Entry'[Amount (LCY)]); FILTER('Detailed Customer Ledger Entry';'Detailed Customer Ledger Entry'[KEY1]='Customer Ledger Entry'[KEY1]))

 And tried to calculate how many overdue receivables are for particular date:

OverDue Receivable = SUMX(
    FILTER('Customer Ledger Entry'; [PastDue]>0);[Receivable, EUR])

But it returns blank, I think it's because PastDue is calculated not row-wise

Maybe you have some tips on how can I solve this question?

PBIX example:

https://drive.google.com/file/d/16gb8jRmd-Fb9OicHUvhZfwkN3k8a1X-Y/view?usp=sharing

[v-yuta-msft]

@Anonymous ,

Create a calculate column [PastDue Column] in table 'Custom legder Entry' instead if measure. Then in table 'Calendar', modify the measure [OverDue Receivable] using DAX as below:

OverDue Receivable = SUMX(
FILTER(ALL('Customer Ledger Entry'), [PastDue Column]>0),[Receivable, EUR])

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 your support, but it's not quite what I needed.

I need to calculate overdue receivable for particular documents and month.

• Firstly to filter all documents till MAX calendar date
• Calculate PastDue date from Due date till max Calendar date
• SUM all receivable where PastDue is more than 0.

And it should be document row dynamic calculations.