Find most recent month from date field

[Taylorcc]

Hello, 

I need to find the number of months between June 2021 and the greatest month in my data set (which will change every quarter)

To begin with the app will have July - September 2020 data in it so the below set analysis yields the correct result but how can I set this to update automatically based on a date field in the app as new data is loaded? 

Eg. Once October-December 2020 data has been loaded, I need 30-09-2020 to change to 31-12-2020

((((year(Date#('30-06-2021','DD-MM-YYYY'))*12)+month(Date#('30-06-2021','DD-MM-YYYY'))) -
(((year(Date#('30-09-2020','DD-MM-YYYY'))*12)+month(Date#('30-09-2020','DD-MM-YYYY')))))

Thank you!

[Saravanan_Desingh]

Try to create a Calendar like this and use it.

SET DateFormat='DD-MM-YYYY';

SET vStartDt='01-01-2020';

Cal:
LOAD Year(AddMonths('$(vStartDt)',IterNo()-1)) As FYear
	 ,AddMonths('$(vStartDt)',IterNo()-1) As FDate
	 ,QuarterEnd(AddMonths('$(vStartDt)',IterNo()-1)) As FQuarterEnd
	 ,QuarterName(AddMonths('$(vStartDt)',IterNo()-1)) As FQuarter
AutoGenerate 1
While IterNo()<=24
;

[Saravanan_Desingh]

Check the FQuarterEnd.

[Taylorcc]

Great thank you! 

I have loaded the script and have the FQuarterEnd field 

How can I use this field to calculate the months between June 2021 and the most recent month in my data? 

My date field is ReferralCommencedDate

[Saravanan_Desingh]

Are you looking something like this?

SET DateFormat='DD-MM-YYYY';

SET vStartDt='01-01-2020';

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

Cal:
LOAD Year(AddMonths('$(vStartDt)',IterNo()-1)) As FYear
	 ,AddMonths('$(vStartDt)',IterNo()-1) As FDate
	 ,QuarterEnd(AddMonths('$(vStartDt)',IterNo()-1)) As FQuarterEnd
	 ,QuarterName(AddMonths('$(vStartDt)',IterNo()-1)) As FQuarter
AutoGenerate 1
While IterNo()<=24
; 

Text Box Expression:

=$(MonthDiff(FQuarterEnd,'01-06-2021'))

[Taylorcc]

This is exactly what I need but it isnt working for me 

=$(MonthDiff(FQuarterEnd,'01-06-2021'))

The fieldname isn't being recognised, it is black rather than the orange/yellow colour? 

[Saravanan_Desingh]

Did you define this Variable in your Script like below?

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

[Taylorcc]

I did, it looks like MonthDiff does work just not this script =$(MonthDiff(FQuarterEnd,'01-06-2021')) 

Thanks so much