how to dynamic calculate the To date based on the next transaction posting date.

Question

Hello community, some help please with interest calculation for monthly debt calls.

I have a report where I would like to determine autonomously the "To" date. The "To" date is determined based on the next transaction posting date.
For example, I have a transaction posting date of 02-10-2021 and the subsequent transaction for this same entity on 05-03-2021. Therefore "To" date for this transaction should be 02-28-2021

Subsequently, the transaction posting date from 05-03-2021, the "To" date will depend on the next transaction posting date. If I have another transaction within the same month, for example occurring on 05-10-2021. Then the "To" date for the subsequent transaction will be 05-09-2021.

Any help with this will be great.

Answer 1

Hi @Mayuran Thiru ,
At first, you need to obtain the next row value of the [From] field through the Lead Function when querying the dataset: SELCT LEAD([From]) OVER (ORDER BY Partner) AS NextValue.

Since the SSRS dataset query does not support the "OVER" statement, the Common Table Expression (CTE) is referenced in the dataset query. The dataset query is as follows:

; WITH cte AS (  
SELECT LEAD([From]) OVER (ORDER BY Partner) NextValue, *  
FROM TableName)  
   
SELECT * FROM cte  

Through this query you will get an extra column [NextValue], which is the value of the next [From] field obtained after sorting according to the [Partner] field.

Then insert a new column [To] on the right side of the [From] field, using the following expression:

=IIF(  
Month(Fields!From.Value)>=Month(Fields!NextValue.Value),   
FORMAT(dateadd("d", -1, Fields!NextValue.Value),"MM-dd-yyyy"),  
FORMAT(DateSerial(Year(Fields!From.Value), Month(Fields!From.Value), "1").AddMonths(1).AddDays(-1),"MM-dd-yyyy")  
)  

The expression means: when the month in the [From] field is equal to the value of the month in the [NextValue] field, the day before the [NextValue] field of the row is displayed; when the month in the [From] field is less than or greater than [NextValue] The value of the month in the field (in your case the [From] field will not be greater than the [NextValue] field), then the last day of the month in the [From] field is displayed.

Design:

Preview:

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

ok thank you.