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

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.

     

     

     

2 Replies

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