cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
goutdelete
Regular Visitor

Calculate late fee with different arrears policy in different period

Hi I have a transaction history table like this and I need to calculate something with different policy under different period of time.

 

Say I'm a librarian and my old policy is that any one can return books late the first two months (doesn't have to be consecutive) without penalty, afterwards every day it's $0.5 dollar late fee per day. 

 

I changed the policy from July 1 to: anyone gets 2-time free pass, but I raised my late fee to $1 per day late. 

 

Later I decided not to give any courtesy policy anymore so as of Oct 1 I changed my policy again to: no free passes, as soon as anyone returns the book late, the late fees become $2 per day.

 

goutdelete_0-1609365418068.png

 

Pretend I know borrower A and C won't be returning books by year end and I'm preparing the total fees owing statement for each person ( i.e A and C will be 20 and 31 days in arrears, respectively).   What's the Dax function I should be using to accomplish the task?

 

In my head I need to produce some counter (or rank) by both transation date as well as by transactional month (since at one point of time the policy doesn't care how many transactions occurs in that two months which also doesn't have to be consecutive).  I googled pretty much all the forum and youtube I can and couldn't find one function that works for me..

 

Much appreciated!!

 

4 REPLIES 4
v-kelly-msft
Community Support
Community Support

Hi  @goutdelete,

 

You could use DATEDIFF function to calculate the date differences between "Target Return date" and "Actual Return date",then multiple the related penalty to get the result you need.

As for the Special July policy,you could create a count column as below:

Count = 
var _actualdate=IF(ISBLANK('Table'[Actual Return date]),DATE(2020,12,31),'Table'[Actual Return date])
Return
 IF('Table'[Target return date]>DATE(2020,7,1)&&'Table'[Target return date]<DATE(2020,10,1)&&_actualdate>'Table'[Target return date],1,0)

Once count=1,it means that row is overdue and meet the policy of July.

As for count the times of overdue respectively,you could use below dax expression in a measure:

_total overdue times=SUMX(FILTER(ALL('Table'),'Table'[Borrower]=MAX('Table'[Borrower])&&'Table'[Date of borrowing]<MAX('Table'[Date of borrowing])),'Table'[Count])

Could you pls identify whether the date period between "date of borrowing"and Target return date or Target return date and Actual Return date will across all the three policies?

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

AllisonKennedy
Super User III
Super User III

@goutdelete  Do you have a policy table with effective dates of your late fee policies? If so, you may want to use an approximate lookup to see what policy is in effect and then base the calculation on that: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html

 

If not, you can hard code the policies into your DAX, might be best to use a switch

 

Which columns do you have already, which do you still need to calculate?



?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hi @AllisonKennedy 

Thanks very much for your feedback.  So this is a "simplied" version of the question actually; currently everything was done in excel and I'm in the middle trying to make it more automate and less manual errors.  Making a policy table with effective date(s) is certainly a good suggestion but I need to give it some more thoughts since other than the cut-off dates it may not be that easy to form a table when the rules are very different (i.e. certain period it's about # of transactions while others it may be # of months, which don't have to be consecutive and doesn't matter how many transactions are there).

 

In my (system) data set I have the first 4 data points, need to compute "days in arrears" in order to move on to the next calc (all manually in excel).  Essentially right now in excel we manipulate "late days/ days in arrears" based on the rules : if an offense can be forgiven then it's ignored (days in arrears = 0).

 

@goutdelete , Try a new  column like this. Change the date as per policy . Used Switch so that you can add more conditions

 

new column =
var _1 = date(2020,07,01)
var _2 = date(2020,10,01)
Var _3 = countx(filter(Table, [Borrower] =earlier([Borrower]) && [Actual Return Date] >= _1 && [Actual Return Date] <=_2
&& [Actual Return Date] <=earlier([Actual Return Date])),[[Actual Return Date]]) +0
return
Switch( true() ,
[Actual Return Date] <=_1 && [Actual Return Date] >=_2 , [Day in Arrears] *2 , // Before july After Oct same
_3 >2 && [Day in Arrears] >0 , [Day in Arrears] * 1 ,
0
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.