Conditional formating using last business day in a month

[Anonymous]

Hi guys, I am designing a report that highlights a date (submission_date) field if it is less than the last business day of the month based on the month filter selected. I tried the following steps: 1. I created a DAX to get last day of the month with this : Last Day of the month = ENDOFMONTH(Query1[Submission_Date].[Date]) 2. I create another DAX to get the date difference between the submission date and the last date of the month using this: DateDiff EOM = DATEDIFF('Query1'[Submission_Date], [Last Day of the month],DAY) 3. I used the DateDiff EOM for the conditional formatting, such that it highlights any date that is less than the last day of each month. This works fine, however, I had a few challenges: a. If the last day of the month is a weekend, I will like the conditional formatting not to highlight the last business day of that month which is a Friday. b. If possible, if the last day of the month is a holiday, I also want the setting in a. above to be affected. Any idea on how to handle this.

[Anonymous · Accepted solution]

Hi @Anonymous,

I'd like to suggest you add calculated column with if statement to return specific color text which used in 'conditional formatting' feature based on 'field value' option.

For example:

Conditional Color = 
VAR lastBusinessDay =
    MAXX (
        FILTER (
            CALENDAR ( DATE ( [Date].[Year], [Date].[MonthNo], 1 ), [Last Day of the month] ),
            WEEKDAY ( [Date], 2 ) <= 5
        ),
        [Date]
    )
RETURN
    IF (
        [Date] <> [Last Day of the month],
        IF (
            [Date] <> lastBusinessDay,
            "Blue",
            IF ( WEEKDAY ( [Date], 2 ) <> 5, "Blue", "Black" )
        ),
        IF ( WEEKDAY ( [Date], 2 ) > 5, "Orange", "Black" )
    )

Conditional formatting in tables

Regards,

Xiaoxin Sheng