DAX dynamic format bars in column chart

[cottrera]

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 month and is date < today

I have a bar chart that displays the property count by year / month

Can the communit recoment a DAX function that would dymanicaly change the bar colour  based on the following criteria
If date is current month = Orange bar
If date is < today but excludes current month = Red bar
thank you Richard

[cottrera]

Thank you for you quick response and solution

[some_bih · Accepted solution]

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.