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

     

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.