Calculate customer running overdue balance

[ansa_naz]

Hi all, I am trying to calculate a running customer Overdue balance (not a running customer balance, which I already have)
Example data is:

Cust_Transactions

Account	Amount	Created	Due	Closed
100000	226.67	01/09/2018	01/10/2018	15/12/2018
100000	125.00	11/11/2018	11/12/2018	18/02/2019
100000	550.35	01/01/2019	01/02/2019	15/05/2019
100000	250.55	09/03/2019	09/04/2019	19/05/2019
100000	655.00	05/04/2019	05/06/2019

What I need to calculate is:

DateTable.Date	Overdue
31/01/2019	125.00
28/02/2019	550.35
31/03/2019	550.35
30/04/2019	800.90
31/05/2019	0.00
30/06/2019	655.00
31/07/2019	655.00
31/08/2019	655.00
30/09/2019	655.00
31/10/2019	655.00
30/11/2019	655.00
31/12/2019	655.00

So at any given Datetable.Date, I need to see what the Overdue balance is for a customer in the current filter context. A transaction is overdue if:

Datetable.Date is greater than Due date, and less than Close date
Or
Datetable.Date is greater than Due date, and Close date is blank

Can anyone please hep with this? Its been bugging me for ages
Many thanks for all help

Edited: - to include blank Close date value in Trans table

[amitchandak]

Please try like

 Current Overdue = CALCULATE(sumx(FILTER(Cust_Transactions,Cust_Transactions[due_date]<=max('Datetable'[Date]) 
 && (Cust_Transactions[close_date]>max('Datetable'[Date]))),Cust_Transactions[Amount]))

In case you have a join; use crossjoin to remove that.

Refer my blog on similar topic

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[ansa_naz]

Hi @amitchandak many thanks for the reply. Is there any way to also show the months where current Overdue =0? Currently, these months are not being shown. My datetable in this instance goes from Jan 2019 to Dec 2019, so ideally I would like to show Jan as 0, and Aug to Dec as 0:

Many thanks for the help

[amitchandak]

Please share formula you created.

[ansa_naz]

Hi @amitchandak current measure is:

Current Overdue =
CALCULATE (
    SUMX (
        FILTER (
            Data,
            Data[due] < MAX ( 'Date'[Date] )
                && ( Data[closed] > MAX ( 'Date'[Date] ) )
        ),
        Data[Amount]
    )
)

Cheers

[amitchandak]

On the join. Try to have cross join

Current Overdue = CALCULATE ( SUMX ( FILTER ( Data, Data[due] < MAX ( 'Date'[Date] ) && ( Data[closed] > MAX ( 'Date'[Date] ) ) ), Data[Amount] )
,CROSSFILTER(Data[due],'Date'[Date],None) )

I am assuming you have join between data[due] and date[date].

Else try crossjoin

[ansa_naz]

Hi @amitchandak , there is no join, if I try to create a join between Data[Due] & Date[Date] I get the following error:

Not sure how I can create this join?

[ansa_naz]

Hi @amitchandak 

I have just noticed that this measure doesnt account for blank Data[Closed].

[amitchandak]

Try if you blank close dates

 Current Overdue = CALCULATE(sumx(FILTER(Cust_Transactions,Cust_Transactions[due_date]<=max('Datetable'[Date]) 
 && (isblank(Cust_Transactions[close_date]) ||  Cust_Transactions[close_date]>max('Datetable'[Date]))),Cust_Transactions[Amount]))

In case there is already a relation make it crossfilter. If it is not there it should be fine.

Refer this article

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[ansa_naz]

Hi @amitchandak so the amounts are correct for blank Closed date now, thank you for that. However, the zero amounts still do not show. I have revised the original post to make this clearer. Please can you advise any further?

[v-joesh-msft · Accepted solution]

Hi @ansa_naz ,

Try formula similar to the following:

 Current Overdue = CALCULATE(sumx(FILTER(Cust_Transactions,Cust_Transactions[due_date]<=max('Datetable'[Date]) 
 && (isblank(Cust_Transactions[close_date]) ||  Cust_Transactions[close_date]>max('Datetable'[Date]))),Cust_Transactions[Amount]))+0

Current Overdue = 
var _co=CALCULATE(sumx(FILTER(Cust_Transactions,Cust_Transactions[due_date]<=max('Datetable'[Date]) 
 && (isblank(Cust_Transactions[close_date]) ||  Cust_Transactions[close_date]>max('Datetable'[Date]))),Cust_Transactions[Amount]))
 return
 IF(_co=BLANK(),0,_co)

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ansa_naz]

Many thanks @v-joesh-msft