Forum Discussion

KBO's avatar
KBO
Memorable Member
4 years ago
Solved

Conditional format based on two dates (with changing values)

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

Need Help
  • mwegener's avatar
    mwegener
    4 years ago

    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"
        )
    )

2 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    4 years ago

    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"
        )
    )