Filter is Excluding Days Needed in Cumulative Total
Hi, all. I have this table with multiple projects.
I used the formula below to calculate a new column called Fiscal Year, based on the following criteria:
Must be the MAX Sequence for the project
Must be an an Action_Name of either “Returned app” or “Completed app”
If the first two criteria apply and the Action_Date is 10/01/2020 – 09/30/2021, then Fiscal Year = “FY21”
If the first two criteria apply and the Action_Date is 10/01/2021 – 09/30/2022, then Fiscal Year = “FY22”
If the first two criteria apply and the Action_Date is 10/01/2022 – 09/30/2023, then Fiscal Year = “FY23”
If nothing applies, then BLANK
Fiscal Year = VAR __Seq = [Sequence] VAR __Project = [Project] VAR __Action = [Action_Name] VAR __Table = FILTER ( 'Table', [Project] = __Project ) VAR __MaxSeq = MAXX ( __Table, [Sequence] ) RETURN IF ( __Seq = __MaxSeq && ( __Action = "Returned app" || __Action = "Completed app" ), SWITCH ( TRUE (), [Action_Date] >= DATE ( 2020, 10, 1 ) && [Action_Date] <= DATE ( 2021, 9, 30 ), "FY21", [Action_Date] >= DATE ( 2021, 10, 1 ) && [Action_Date] <= DATE ( 2022, 9, 30 ), "FY22", [Action_Date] >= DATE ( 2022, 10, 1 ) && [Action_Date] <= DATE ( 2023, 9, 30 ), "FY23", BLANK () ), BLANK () )
The problem with this formula is that it’s causing an error in the cumulative total of Days_between_Actions, since it excludes certain days from the calculation. The Days_between_Actions column is used to calculate the total number of days to complete a project (e.g., Project A took 24 days to complete [13 + 11 = 24]). The current formula for Fiscal Year calculates Project A as 11 days instead of the correct 24. How can I adjust the formula to ensure the Days_between_Actions column isn't affected?