Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Color Background for Line/Bar chart

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...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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 )

     

colacan's avatar
colacan
Resolver II
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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. 🙂