Conditional formatting on the X-axis

[Lukep]

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

[FreemanZ · Accepted solution]

hi @Lukep ,

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:

[Lukep]

Hi @FreemanZ ,

That has worked perfectly.

Thank you.

[eliasayyy]

hello @Lukep ,

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

[Lukep]

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)