Hello,
I'm trying to filter down to the maximum value of a column based on filtering two other columns. More specifically, the maximum fiscal period (FP) number for the actual ("ACT") scenario in the current year (FY). All three of the expression I tried return a blank value:
Max Period:=CALCULATE(MAX(Table[FP]),FILTER(Table,Table[FY]=MAX(Table[FY])),Table[Scenario]="ACT")
Max Period:=CALCULATE(MAX(Table[FP]),FILTER(Table,Table[FY]=MAX(Table[FY])),FILTER(Table,Table[Scenario]="ACT"))
Max Period:=CALCULATE(MAX(SPDData[FP]),FILTER(SPDData,AND(SPDData[Scenario]="ACT",SPDData[FY]=MAX(SPDData[FY]))))
If I remove the scenario filter it returns 12 as expected, but I need to return 2. What am I doing wrong here?
... with repeating data for prior years
I'm trying to filter down to the maximum value of a column based on filtering two other columns. More specifically, the maximum fiscal period (FP) number for the actual ("ACT") scenario in the current year (FY). All three of the expression I tried return a blank value:
Max Period:=CALCULATE(MAX(Table[FP]),FILTER(Table,Table[FY]=MAX(Table[FY])),Table[Scenario]="ACT")
Max Period:=CALCULATE(MAX(Table[FP]),FILTER(Table,Table[FY]=MAX(Table[FY])),FILTER(Table,Table[Scenario]="ACT"))
Max Period:=CALCULATE(MAX(SPDData[FP]),FILTER(SPDData,AND(SPDData[Scenario]="ACT",SPDData[FY]=MAX(SPDData[FY]))))
If I remove the scenario filter it returns 12 as expected, but I need to return 2. What am I doing wrong here?
... with repeating data for prior years
