Skip to Content
0
Jan 02, 2019 at 04:33 PM

Fiscal Year report calculation issue

42 Views

Have a report that works great for calendar year but once the calendar flips to January the YTD values from prior months no longer work. The report is formatted as a cross table.

Example (# would equal a value in each month and ytd column):

Month Name MTD | YTD

Sept ## | ##

Oct

Nov

Dec

Jan

Totals: ## | ##

Report has the following variables:

Month: =MonthNumberOfYear([Date & Time])

Month Name:=Month[Discharge Date & Time])

Year: =Year([Date & Time])

End Date: =ToDate([UR];"")

End Month: =MonthNumberOfYear([EndDt])

End Year: =Year([EndDt])

UR: =UserResponse("End Date:")

YTDCount: =[Count] Where([Year] = [EndYear] And [Month] <= [EndMonth])+0

MTDCount: =[Count] Where([Year] = [EndYear] And [Month] = [EndMonth])+0

As of January the value for prior months YTD is 0. Tried adding -1 to the count variable after month but did not affect the result. Looking for a way to capture the prior months values. The current month MTD and YTD values work for 2019, not 2018. Otherwise would need a separate report for 2019. Not ideal.