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 ## | ##
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.