Conditional format based on two dates (with changing values)

[KBO]

Hi,

I guess someone will tell me that this is a very easy one but I tried a lot of things and hope that someone can help me ;).

Problem:

I have a traffic light logic in my model (which is working fine) - now I like to match this logic with the dates from the Logic Table (Start and End). 

I build a measure for this too but this won't work like I like :). 

For example If I have choose the 03/03/2021 then the color should change to green (because if we go into the Logic Table the values between 01/01/2021 and 30/06/2021 should be green if its under 0,9

If I choose the 07/07/2021 then the color should change into orange because we have a new range in the Logic table from 01/07/2021 to 03/03/2022 which says that every values over 0,5 should be orange

I hope someone can help with this case :D.

Best,

Kathrin

[KBO]

Thanks @mwegener ,

a hero as always ;).

Best,

Kathrin

[mwegener · Accepted solution]

Hi @KBO ,

I have first cleaned up the relationships a bit.

After that I created the following measure for formatting. I hope it helps.

TL = 
VAR _Logic =
    FILTER (
        Logic,
        Logic[Start] <= SELECTEDVALUE ( 'Date'[End of Month] )
            && Logic[End] >= SELECTEDVALUE ( 'Date'[End of Month] )
    )
VAR _Value = [Total Values]
RETURN
    MAXX (
        _Logic,
        SWITCH (
            TRUE (),
            _Value <  [Green ], "green",
            _Value <= [Yellow], "orange",
            _Value >  [Red], "red",
            "grey"
        )
    )