Forum Discussion

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

DAX dynamic format bars in column chart

Hi    I have a facts table that shows property equipment and the date a service is next due. This is conncted to a calendar table with the following columns - Date, Year, Month ,Month# , Is current...
Need Help
  • some_bih's avatar
    some_bih
    3 years ago

    Hi cottrera possible solution as following

    In your Calendar / Date table insert column to create values later used in conditional formatting

     

    Type of Month =
    VAR __year=YEAR('Date'[Date])
    VAR __month=MONTH('Date'[Date])
    VAR __year_month_number=__year*12+__month-1

    VAR __year_current=YEAR(TODAY())
    VAR __month_current=MONTH(TODAY())
    VAR __year_month_number_current=__year_current*12+__month_current-1

    VAR __Result=
    IF(
       __year_month_number = __year_month_number_current ,
       "Current month",
       --"#FFA500",--orange
       IF(
         __year_month_number <__year_month_number_current ,
         "Month before",
         -- "#ff0000", --red
         "Future month"
         -- "#000000"  --black
      )
    )
    RETURN __Result

     

    To go to cond.formatting on bar visual put your measure (in my case it was dummy measure simple SUM (some column) and select as first picture below. 

    After that, go to second picture and choose format style based on rules and in part What field should we base this on? choose just created column in Calendar / Date table "Type of Month".

    Choose your colour pallete as you wish.

    Did I answer correctly? Kudos appreciate / accept solution.

     

     

     

some_bih's avatar
some_bih
Icon for Community Champion rankCommunity Champion
3 years ago

Hi cottrera possible solution as following

In your Calendar / Date table insert column to create values later used in conditional formatting

 

Type of Month =
VAR __year=YEAR('Date'[Date])
VAR __month=MONTH('Date'[Date])
VAR __year_month_number=__year*12+__month-1

VAR __year_current=YEAR(TODAY())
VAR __month_current=MONTH(TODAY())
VAR __year_month_number_current=__year_current*12+__month_current-1

VAR __Result=
IF(
   __year_month_number = __year_month_number_current ,
   "Current month",
   --"#FFA500",--orange
   IF(
     __year_month_number <__year_month_number_current ,
     "Month before",
     -- "#ff0000", --red
     "Future month"
     -- "#000000"  --black
  )
)
RETURN __Result

 

To go to cond.formatting on bar visual put your measure (in my case it was dummy measure simple SUM (some column) and select as first picture below. 

After that, go to second picture and choose format style based on rules and in part What field should we base this on? choose just created column in Calendar / Date table "Type of Month".

Choose your colour pallete as you wish.

Did I answer correctly? Kudos appreciate / accept solution.