IF formula to filter based on Fiscal Year

[fernandoC]

Hi!

I want to create an IF formula so that it filters based on a particular fiscal year. Also I want depending on the Fiscal Year a particular SUM on a table is triggered. 

This is the formula that I'm using (that doesn't work 😞 😞 

Training Hours Test = CALCULATE(IF(ISFILTERED('Fiscal Year Calendar'[Fiscal Year]) = "FY19-20",SUM('Chisinau 2 FY1920'[# of Employees]),SUM('Chisinau 2 FY2021'[# of Employees]) ))

it gives me the following error:  "Dax Operations do not support comparing values of type TRUE/FALSE with values of type text".

These are the two tables that I want to trigger when a particular fiscal year is filtered : IF FY19-20 then SUM # of Employees from Table 1 OR IF FY20-21 then SUM # of Employees from table 2. (Would like to do this not only for these fiscal years but for any subsequent one i.e FY21-22, etc.)

[Greg_Deckler]

@fernandoC First, I think you want:

Training Hours Test = IF(ISFILTERED('Fiscal Year Calendar'[Fiscal Year]) = "FY19-20",CALCULATE(SUM('Chisinau 2 FY1920'[# of Employees]),SUM('Chisinau 2 FY2021'[# of Employees]) ))

[fernandoC]

Hi @Greg_Deckler,

Thank you for your quick response. 

I've made some changes to both tables and appended them altogether as I needed it for other calculations. Based on your formula I changed it to: 

IF(ISFILTERED('Fiscal Year Calendar'[Fiscal Year]) = "FY19-20",CALCULATE(SUM('Training Costs'[# of Employees])))

When trying this formula I still get the error: Dax comparison operations do not support comparing values of type true/false with values of type text. Also, would like to be able to have multiple filters in the formula such as FY19-20,FY20-21,FY21-22 if at all possible. 

This is what it looks like now:

Name of table: 'Training Costs'

[Greg_Deckler · Accepted solution]

@fernandoC Oh, sorry, missed that in the formula, was so focused on the other issue, you probably want:

IF('Fiscal Year Calendar'[Fiscal Year] = "FY19-20",CALCULATE(SUM('Training Costs'[# of Employees])))

or:

IF(MAX('Fiscal Year Calendar'[Fiscal Year]) = "FY19-20",CALCULATE(SUM('Training Costs'[# of Employees])))

ISFILTERED returns true/false if the column is filtered or not. So, you can't compare that to a text value. That is what it was complaining about. Use the second version if this is a measure. 

[fernandoC]

Thanks Greg! it works. 

[fernandoC]

For some reason the tables were not attached on my original comment: