Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Conditional formatting on the X-axis

Hi everyone,

 

I am trying to do a report that will automatically update at the start of each month. Within this report there are some charts, like the one attached. 

 

For the graph selected i want it to match the legend below using conditional formatting. This is because every month i want the colour to remain the same, whereby the earliest month in the data set is orange, the latest month in the data set is blue and the other month is black. I have been finding it difficult to write the correct DAX that will do this. Is it possible or is there perhaps a way to work around it. I am also attaching two more screenshots if they help?

 

 

Any help would be appreciated

 

Thank you,

Luke

Help with conditional formatting
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi Anonymous ,

     

    Not sure if i fully get you. Supposing you have a data table like:

    date amt
    12/1/2022 1
    12/2/2022 1
    1/1/2023 2
    1/2/2023 2
    2/1/2023 3
    2/2/2023 3
    3/1/2023 4
    3/2/2023 4
    4/1/2023 5
    4/2/2023 5
    5/1/2023 6
    5/2/2023 6


    Try to

    1) create a calculated dates table like below and connect with data[date]. 

     

    dates = 
ADDCOLUMNS(
    CALENDAR(MIN(data[date]), MAX(data[date])),
    "YYMM", FORMAT([Date], "yy/mm")
)

     

     

    2) create another calculated slicer table like below and keep it unrelated:

     

    slicer = dates

     

     

    3) plot a slicer with slicer[date] column;

     

    4) plot a column chart with dates[yymm] and data[amt] column, and apply conditional formatting for the column color with a measure like:

     

    Color = 
VAR _minsliceddate = MIN(slicer[date])
VAR _maxsliceddate = MAX(slicer[date])
VAR _minslicedmonth = YEAR(_minsliceddate)*12+MONTH(_minsliceddate)
VAR _maxslicedmonth = YEAR(_maxsliceddate)*12+MONTH(_maxsliceddate)
VAR _currentdate = MAX(dates[date])
VAR _currentmonth = YEAR(_currentdate)*12+MONTH(_currentdate)
VAR _result =
SWITCH(
    TRUE(),
    _currentmonth =_minslicedmonth, "Orange",
    _currentmonth=_maxslicedmonth, "Blue",
   _currentmonth=_maxslicedmonth-1, "Black",
    "Gray"
)
RETURN _result

     

     

    it worked like:

     

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    hi Anonymous ,

     

    Not sure if i fully get you. Supposing you have a data table like:

    date amt
    12/1/2022 1
    12/2/2022 1
    1/1/2023 2
    1/2/2023 2
    2/1/2023 3
    2/2/2023 3
    3/1/2023 4
    3/2/2023 4
    4/1/2023 5
    4/2/2023 5
    5/1/2023 6
    5/2/2023 6


    Try to

    1) create a calculated dates table like below and connect with data[date]. 

     

    dates = 
ADDCOLUMNS(
    CALENDAR(MIN(data[date]), MAX(data[date])),
    "YYMM", FORMAT([Date], "yy/mm")
)

     

     

    2) create another calculated slicer table like below and keep it unrelated:

     

    slicer = dates

     

     

    3) plot a slicer with slicer[date] column;

     

    4) plot a column chart with dates[yymm] and data[amt] column, and apply conditional formatting for the column color with a measure like:

     

    Color = 
VAR _minsliceddate = MIN(slicer[date])
VAR _maxsliceddate = MAX(slicer[date])
VAR _minslicedmonth = YEAR(_minsliceddate)*12+MONTH(_minsliceddate)
VAR _maxslicedmonth = YEAR(_maxsliceddate)*12+MONTH(_maxsliceddate)
VAR _currentdate = MAX(dates[date])
VAR _currentmonth = YEAR(_currentdate)*12+MONTH(_currentdate)
VAR _result =
SWITCH(
    TRUE(),
    _currentmonth =_minslicedmonth, "Orange",
    _currentmonth=_maxslicedmonth, "Blue",
   _currentmonth=_maxslicedmonth-1, "Black",
    "Gray"
)
RETURN _result

     

     

    it worked like:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi FreemanZ ,

       

      That has worked perfectly.

       

      Thank you.

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    2 years ago

    hello Anonymous ,

     

    you could try to create a measure that forms the conditional formatting for you

     

    measure = if( AND([month] = 9, [year] = 2022), "orange", if(AND([month] = 9, [year] = 2023),"blue","black"))

    then add this measure in the fx color setting in your bar as a field value option and you will get the colors for each legend

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi eliasayyy ,

       

      I need a measure that will still work without me having to go in and change anything when the data automatically updates at the start of each month. for example the most recent months report is October (10)