Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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
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
Please share formula you created.
Hi @amitchandak current measure is:
Current Overdue =
CALCULATE (
SUMX (
FILTER (
Data,
Data[due] < MAX ( 'Date'[Date] )
&& ( Data[closed] > MAX ( 'Date'[Date] ) )
),
Data[Amount]
)
)
Cheers
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
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?
Hi @amitchandak
I have just noticed that this measure doesnt account for blank Data[Closed].
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
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
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |