Get active equipment count using a date parameter

I currently have a report that I run with 2 date prompts a starting date and an ending date prompt where I select a dd/mm/yyyy for each.

In this report, I have a formula that is counting active equipment records at the end of the reporting period.

if (IsNull({Equipment.CreatedOn}) Or Date({Equipment.CreatedOn}) <= {?Ending Date}) AND
    Not ({Equipment.Inactive} and (IsNull({Equipment.RetireDate}) Or Date({Equipment.RetireDate}) <= {?Ending Date}))
then 1
else 0

I then sum this formula and it gives me the correct count.

I am working on a new report that I do not want to have a beginning and ending date parameter, I only want to have a Year parameter.

I am breaking down the counts of equipment that were added each quarter.
I have this part working.

I would like to get the total Active count at the end of the period to be based on the Year parameter.

If I were to run the report for the current year, I am able to easily report active as of today/currentdate.

What I am hoping to achieve is that if I run the report for a previous year like 2020, I will get the count of equipment as of 12/31/2020.

If my prompt is only asking YYYY, how can I get it to show the last day of the year???

Sorry for the very long explanation, hope it makes sense.


I think I was able to get it figured out. I just changed my original formula a little bit to:

if (IsNull({Equipment.CreatedOn}) Or Month({Equipment.CreatedOn})<=12 and Day({Equipment.CreatedOn}) <=31 and Year({Equipment.CreatedOn}) <= {?Year})
AND
Not ({Equipment.Inactive} and (IsNull({Equipment.RetireDate}) Or Year({Equipment.RetireDate}) <= {?Year}))
then 1
else 0

Seems to be working. I will do more testing.


Edited by Tonyak74 - 19 Jul 2022 at 12:01pm

You can simplify the formula. Because all months will <= 12 and all days will be <= 31, you don't have to check for that. So, changing your formula to this will work and will decrease the amount of processing required:

if (IsNull({Equipment.CreatedOn}) Or Year({Equipment.CreatedOn}) <= {?Year})

AND

Not ({Equipment.Inactive} and (IsNull({Equipment.RetireDate}) Or Year({Equipment.RetireDate}) <= {?Year}))

then 1

else 0

-Dell