How to Highlight Past months

[dharish20240911]

Highlight the past months in Line and stacked column chart using DAX.

[v-heq-msft · Accepted solution]

Hi @dharish20240911,
Thanks for Kaviraj11 reply.
You can try the following steps
Sample data

Date Value1 Value2
5//1/2024 5 7
6/1/2024 4 8
7/1/2024 7 4
8/1/2024 3 9
9/1/2024 9 5

Create a mesasure

IsCurrentMonth = 
IF(
    MONTH(SELECTEDVALUE('Table'[Date])) < MONTH(TODAY()),
    "Red",
    "Black"
)

Apply the measure to the format

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[dharish20240911]

This above solution is also highting the months for Oct , Nov and December if the current month is Sep

[v-heq-msft]

Hi @dharish20240911 ,
I've added a couple of later dates to the original data and still only highlight the first few months, so you can check that all your steps are working.

Best regards,
Albert He

[Kaviraj11]

Hi,

Step 1: Create a Date Table

Ensure you have a Date table in your model. If not, you can create one using DAX:

DateTable = 
ADDCOLUMNS (
    CALENDAR (DATE(2020, 1, 1), DATE(2024, 12, 31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "MonthYear", FORMAT([Date], "MMM YYYY")
)

Step 2: Create a Calculated Column to Identify Past Months

Add a calculated column to your Date table to flag past months:

IsPastMonth = 
IF (
    [Date] < TODAY(),
    "Past",
    "Current/Future"
)

Step 3: Use the Calculated Column in Your Chart

1. Add your Line and Stacked Column chart to the report.
2. Add the necessary fields to the chart (e.g., Date, Values).
3. Drag the IsPastMonth column to the Legend or Axis field well to differentiate between past and current/future months.

   Step 4: Format the Chart

   1. Go to the Format pane.
   2. Under Data colors, set different colors for “Past” and “Current/Future” to highlight the past months.

      Example

      Here’s an example of how your data might look:

      Date       | Value | IsPastMonth
      -----------|-------|------------
      2024-01-01 | 100   | Past
      2024-02-01 | 150   | Past
      2024-03-01 | 200   | Past
      2024-04-01 | 250   | Current/Future
      2024-05-01 | 300   | Current/Future

      In the chart, the past months (January, February, March) will be highlighted differently from the current/future months (April, May).

      This approach ensures that your past months are visually distinct in the Line and Stacked Column chart, making it easier to analyze trends over time.