Please provide solution in power BI, Power Query
Required Outstanding balance & Penalty as per T+4 working day excluding customize state wise holiday.
Holiday in Gujarat on 08-01-2022, 09-02-2022
Examlpe : Previous Outstanding on 11-01-2022 is (500000) Invoicing done on 06-01-2022 of 450000 is due on 12-01-2022 then Outstanding on 12-01-2022 is (50000) and invoicing done on 07-01-2022 of 100000, 08-01-2022 of 45000, 09-01-2022 of 550000 is due on 13-01-2022 then Outstanding is 645000 where penalty is 1000 (Penalty > 5 Lakhs < 10 Lakhs = Rs 1000 ) as per T+4 (transaction + 4 days) credit period.
Required Outstanding balance & Penalty as per T+4 working day excluding customize state wise holiday.
Also Any Invoice done on particular date will be due before 5 working day for T+4 credit period. Penalty amount for outstanding is slab wise
Outstanding Amount
Penalty (per day)
< 5 Lakhs = Rs 500
> 5 Lakhs < 10 Lakhs = Rs 1000
> 10 Lakhs < 50 Lakhs = Rs 2000
> 50 Lakhs = Rs 3000
Required colunm
Posting Date = Invoice Date
RV= Invoice value
ZT =Payment received
Running balance = Outstanding balance as on date.
Penalty = Penalty Amount
