Hello @Jim Dehner ,
I am trying to find the number of Continuing parking permits in my report - the permits issued from September 1st of the current fiscal year. We follow the Fiscal year from Sept 1st to Aug 31st.
I created the following formulas to create the continuing field to calculate these records, please let me know if this is correct. I see a slight difference in the count when I validate my results, so I am trying to understand where I am going wrong in the calculations.
1. Created Date -FY field (by setting the default properties to Fiscal year "Sept")
[Issue date]
2. Created Date-FY Start date field
IF (MONTH(TODAY()-1) >= MONTH(#September 1, 2021#)
AND
MONTH(TODAY()-1) <= MONTH(⌗December 31, 2021#))
THEN DATE ("09/01/"+DATENAME('year', TODAY()-1))
ELSEIF
(MONTH(TODAY()-1) >=MONTH(#January 1, 2022#)
AND
MONTH(TODAY()-1) <= MONTH(⌗August 31, 2022#))
THEN DATE ("09/01/"+DATENAME('year', DATEADD('month',-12,TODAY()-1)))
END
3. Created the continuing permits field
IF [Date-FY]< [Date-FY start date] THEN 0
ELSEIF [Date-FY]> (TODAY()-1)THEN 0
ELSE [TotalCount]
END
Attached is the workbook, please let me know if you are able to access and view the data with calculations.
Any help would be appreciated. Thanks!
While helpful, the screenshot doesn't show us how the result is being arrived at for Continuing vs. New vs Final...we don't have the business model or background which derives that...
To simplify, can you bring that particular row of data down to FY-2022 that you have highlighted in your example for FF? If I compare for same row in Tableau, which means it starts at 9/1/2021 and carries through to current month March 7, 2022, then you should have the following data for Total Count:
Which would mean the FY is working...which was the original concern...
As example, if I apply the original calculation you're using for 'Continuing' with the new FY setting in place, I get the same result:
The same as just using Total Count...
So, I think need to better define what is considered Continuing, or New, etc.... OK I'm headed off to another project will look at this thread later. D
