SSRS Statement Due this Month Calculation

Epicor ERP 10
, , , ,
1

Hi

I have a calculation where i need to look at the month only and am struggling to write it.

On the statement i need the due date to be more than the statement date but less than and equal to the next month, to then sum up the values.

This is what i have…
=SUM(IIF(CDate(Fields!DueDate.Value) > CDate(Fields!Calc_StatementDate.Value) And CDate(Fields!DueDate.Value) <= DateAdd(“m”, 1, Fields!Calc_StatementDate.Value),Fields!RunningDocInvoiceBal.Value, CDec(0)))

Problem is if the statement date is the 28th Feb but the due date is 30th March this will then fall into “not yet due” as DateAdd(“m”, 1, Fields!Calc_StatementDate.Value) equals 28th March.

I need if 30th march (DueDate) is more than 28th Feb (statement date) but less than and equal to March (statement month only plus 1) then sum balance.

Any ideas would be great. I know we have the aging but we currently have an issue that isnt being fixed just yet.

This will also be great to know for anything i need to use this on.

Regards
Kirsty

2

Or the last day of the next month so 31st march

i have found this calculation and believe this works although i need to do more testing but can someone explain what this calculation is doing as i dont understand the addmonth(2) and adddays

=SUM(IIF(CDate(Fields!DueDate.Value) > CDate(Fields!Calc_StatementDate.Value) And CDate(Fields!DueDate.Value) <=Format(DateSerial(Year(Fields!Calc_StatementDate.Value), Month(Fields!Calc_StatementDate.Value), “1”).AddMonths(2).AddDays(-1),“dd/MM/yyyy”), Fields!RunningDocInvoiceBal.Value, CDec(0)))

3

Lets say Calc_StatementDate is Feb 28th

Format(DateSerial(Year(Fields!Calc_StatementDate.Value), Month(Fields!Calc_StatementDate.Value), “1”).AddMonths(2).AddDays(-1),“dd/MM/yyyy”)

This part is basically saying take the first day of the Calc_StatementDate month, then add 2 months, decrease 1 day and format the result as dd/MM/yyyy

So Feb 28th becomes Feb 1st, after adding 2 months it becomes April 1st, then decrease a day to March 31st.

4

Thank you so much for your reply. That now makes perfect sense. Thank you again :slight_smile:
Regards
Kirsty.

5

That was neat. I had to lookup DateSerial. And then if I break it into constituent parts it does kinda flow:

Format(
	DateSerial(
		Year(Fields!Calc_StatementDate.Value),
		Month(Fields!Calc_StatementDate.Value),
		“1”)
	.AddMonths(2).AddDays(-1),
	“dd/MM/yyyy”)

Syntax
DateSerial(year, month, day)
The DateSerial function syntax has these named arguments:
Part	Description
year	Required; Integer.
month	Required; Integer.
day		Required; Integer.

Calc_StatementDate = Feb 28, 2021

Format(
	DateSerial(
		2021,
		2,
		1)
	.AddMonths(2)  {2021,4,1}
	.AddDays(-1),  {2021,3,31}
	“dd/MM/yyyy”)  {31/03/2021}