Forum Discussion

PBInewbie17's avatar
PBInewbie17
Helper I
3 years ago
Solved

Filter Multiple Criteria

Hi, all. I have the following table with multiple projects. I need a new column called Fiscal Year that is calculated based on the following criteria:  Must be the MAX Sequence for the proje...
Filter
Max
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    PBInewbie17 Try:

    FY Column =
  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),"FY21",      
      [Action_Date] >= DATE(2022,10,1) && [Action_Date] <= DATE(2023,9,30),"FY21",
      BLANK()
    ),
    BLANK()
  )
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

PBInewbie17 Try:

FY Column =
  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),"FY21",      
      [Action_Date] >= DATE(2022,10,1) && [Action_Date] <= DATE(2023,9,30),"FY21",
      BLANK()
    ),
    BLANK()
  )