Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Overdebt receivables for particular date

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

 

Need Help

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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.