Color Background for Line/Bar chart

[Anonymous]

Hi guys,

I'm trying to achieve something for a while now and that is creating a traffic light background for a client.

The bottom and middle part (red and yellow color) were easy to implement but the green top I can't get a straight line. I've been trying to calculate the MAX value and set that as the top line, but it's proving to be difficult with the year-month roll-back (because I'm using the dynamic 12-month roll-back function by Alberto Ferrari). Not sure if I'm missing some filters to be taken out of the calculation when calculating the MAX function 😞 so it should look at the max value in the 12 months back based on slcier selection. So if I select 202212 it should check the values from 202201 to 202212, find the MAX, and then put that MAX value to all year-month combinations in the graph. Any help and ideas are appreciated! 🙂

[Anonymous · Accepted solution]

The solution by the one and only Alberto Ferarri! 🙂 

You're welcome, guys.   

CALCULATIONITEM "12 months max" =
        VAR NumOfMonths = 12
        VAR ReferenceDate = MAX ( 'Date'[Date] )
        VAR PreviousDate =
            DATESINPERIOD ( 'Date'[Date], ReferenceDate, -NumOfMonths, MONTH )
        VAR ShowValue =
            NOT ISEMPTY ( INTERSECT ( PreviousDate, VALUES ( 'Previous Date'[Date] ) ) )
        VAR Result =
            CALCULATE (
                MAXX ( VALUES ( 'Date'[Year Month] ), SELECTEDMEASURE ( ) ),
                PreviousDate,
                ALL ( 'Date' )
            )
        RETURN
            IF ( ShowValue, Result )

[v-jianboli-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, it's not quite solved yet, I will clean up the dataset and publish the pbix file, since I think that will make things easier 🙂

[colacan]

Hey, if your green value shows the value for 202212 instead of Max value between 202201~202212, you should mofify the Filter Context when you make the measure using Colculate.
like, Calculate(max(greenvalue),dateinperiod(date[date],date[date],-12,month)).  it modifies the [date] filter from curretn date to last 12 months period. To learn about filter modifier:
https://docs.microsoft.com/en-us/learn/modules/dax-power-bi-modify-filter/2-modify-filter-context

Hope this helps you.

Thanks
 

[Anonymous]

Hi @colacan 

Thank you so much for this advice. However, the tricky part is that I'm trying to find the max value of a measure, not a column. Although you have given me a good idea with trying the DATESINPERIOD, so something in that line might work, I just have to further tweak the formula. 🙂