Thank you Don,

I'm attaching a twbx file and elaborating a little more.

1. My dataset has a dimension, Year (Fall 2017, Fall 2018, Fall 2019). I know, it should be a date field when I call it year, but its a text field because I am specifying it based on seasons.

2. I want to create a calculation Yield by each year (basically seeing how many customer out of all the 32 that we reached out to, made a purchase; see Yield calculated field in the twbx file )

3. I want a table that shows Yield by Year. Up until this point it is easy

4. This is the main question; If the Yield Fall 2019 is less than Yield in Fall 2018, I want it's font to be red, if not, then blue.

I've experimented with If statements in the calc field (If Year = Fall 2019, then "calculated Yield....) and then pulling it in into Color Mark, but it tells me that i cannot combine aggregate and non-aggregate functions.

I would really appreciate your help.

thanks

Thank you Don,

This really helps. I used the 2nd method and color-coded it and it works. I modified the formula to specify that if Fall 2019 Yield is 0.5 less than Fall 2018, it should be red.

IF FIRST()=0 THEN 'first year' ELSEIF

LOOKUP(ZN([Yield]), -1)-ZN([Yield]) >0.5 THEN 'Below' ELSE 'Above' END

I am trying to refine it further because essentially I am only concerned with Fall 2019 falling below Fall 2018. I'm not bothered if the Fall 2018 Yield is less than Fall 2017 as its old news. I tried the following modification in the calculation. Does it seem like its working?

IF FIRST()=0 THEN 'first year'

ELSEIF First ()=-1 then 'second year'

ELSEIF First ()=-2

And LOOKUP(ZN([Yield]), -1)-ZN([Yield]) >0.5 THEN 'Below' ELSE 'Above' END

Thank you so much for your help

Salma

This is really helpful Don. Thank you very much.

There's an additional piece to this (pls do let me know if I should't continue here and instead open a new question. I'm putting it here because it is related).

If we have further groupings besides "Year" , say "Industry", how can I apply the same conditional formatting. Essentially, I want to compare Fall 2019,  Industry 3 Yield with Fall 2018,  Industry 3 Yield, and if the Fall 2019 is less than Fall 2018, I want the Yield value to be turned red. I would like to replicate it for Industry 1 and 2.  I used the existing color calc field and it seems to be comparing Fall 2017 Industry 3 with Fall 2017 Industry 3.

Thank you