Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
KBO
MVP

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

1 ACCEPTED SOLUTION

Hi @KBO ,

I have first cleaned up the relationships a bit.

TrafficLight.png

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"
        )
    )
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

2 REPLIES 2
KBO
MVP

Thanks @mwegener ,

a hero as always ;).

 

Best,

Kathrin

Hi @KBO ,

I have first cleaned up the relationships a bit.

TrafficLight.png

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"
        )
    )
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors