Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
dharish20240911
New Member

How to Highlight Past months

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

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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

vheqmsft_6-1726127580022.pngvheqmsft_7-1726127602602.png

vheqmsft_8-1726127612728.png
Final output

vheqmsft_9-1726127642025.png

 

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

 

 

 

View solution in original post

4 REPLIES 4
v-heq-msft
Community Support
Community Support

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

vheqmsft_6-1726127580022.pngvheqmsft_7-1726127602602.png

vheqmsft_8-1726127612728.png
Final output

vheqmsft_9-1726127642025.png

 

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

 

 

 

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

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.

vheqmsft_0-1727314321140.png

Best regards,
Albert He

 

 

Kaviraj11
Super User
Super User

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.