Forum Discussion

dharish20240911's avatar
dharish20240911
New Member
1 year ago
Solved

How to Highlight Past months

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

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      dharish20240911
      New Member
      1 year ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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's avatar
    Kaviraj11
    Solution Sage
    1 year ago

    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.