Calculate late fee with different arrears policy in different period

[goutdelete]

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.

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!!

[v-kelly-msft]

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]

@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?

[goutdelete]

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).

[amitchandak]

@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
)