For whatever reason, PastYear is returning 0 for contribution amounts. I've got a pretty simple pivot set up and a simple use of the function, but it's returning 0.
My real goal is to show where we were on fundraising by fund last fiscal versus COVID, but it was tough for me to get the numbers to build the real formulas without being able to compare year-to-year.
Hi Tim,
The PastYear and other date related Functions in Analytics only work when a literal date type field is used against it. And those date type fields in Analytics are all calendar dates. I'm assuming based on your OP that we're looking at FY 2019 and FY 2020, which in Analytics are simple integers. The date related Functions unfortunately won't interpret those as dates and do their magic.
Assuming that using the [Date] field grouped up to the calendar year level won't due, I suggest a variance formula such as:
CASE WHEN [Max Fiscal Year] = 2020 THEN ( [Total Amount] , [Fiscal Year = 2019] ) - ( [Total Amount] , [Fiscal Year = 2020] )WHEN [Max Fiscal Year] = 2019 THEN ( [Total Amount] , [Fiscal Year = 2018] ) - ( [Total Amount] , [Fiscal Year = 2019] )...ELSE NULL END