FY Comparison formula 1

[jchewsmith]

I am trying to create a report that counts records based on a Fiscal Year to Date formula. First current FYTD through the prior month end, then Last FYTD through the prior month end, then a formula with the difference between the two.

How do I write a formula to count based on Beginning FY through last full month end, then another formula for the same months of the prior year?

Example if I ran report today it would pull July 1, 2017 to Sep 30, 2018 for FYTD, then July 1, 2016 to Sep 30, 2017 for Last FYTD.

 

[lbass]

Your example doesn't really match your description, since you are showing 15 month periods that cross fiscal years. I'm assuming you meant FY to date for this year vs last.

First create a formula for Fiscal Year:

//{@FY}:
year(currentdate+184)

Then create a formula for each year period:

//{@currentFYthruLFM}:
if {table.date} in date({@FY}-1,7,1) to maximum(LastFullMonth) then 1

//{@LastFYthruLFM}:
if {table.date} in date({@FY}-2,7,1) to dateserial(year(currentdate)-1,month(currentdate)-1,day(currentdate-day(currentdate)))
then 1

//{@diff}:
sum({@currentFYthruLFM})-sum({@LastFYthruLFM})

If you want the difference at some group level, you would need to add the group condition to the diff formula:

sum({@currentFYthruLFM},{table.grpfield})-sum({@LastFYthruLFM},{table.grpfield})

-LB