Condition formatting or advanced DAX of s-curve lines

[kylee_anne]

Hi,

I have this visual.

And the DAX for my yellow line is:

P6 Remaining Units Curve running total in Date (month bins) =

CALCULATE(

    CALCULATE(sum('Resource M'[Value]),'Resource M'[Spreadsheet Field]="Actual Units"),

    FILTER(

        ALLSELECTED('Date'[Date (month bins)]),

        ISONORAFTER('Date'[Date (month bins)], MAX('Date'[Date (month bins)]), DESC)

    )

)

+

CALCULATE(

    [P6 Remaining Units Curve],

    FILTER(

        ALLSELECTED('Date'[Date (month bins)]),

        ISONORAFTER('Date'[Date (month bins)], MAX('Date'[Date (month bins)]), DESC)

    )

)

Sorry a bit clumsy but it works.

What I want is to make first half of the line, less than a certain date or the actual from the DAX, transparent and the other half to remain yellow.  But I can't think of the best way to do this.  All help appreciated.

[Sandip_Palit · Accepted solution]

Power BI does not support direct transparency for line segments, so this workaround uses conditional logic to split the visual representation based on a defined date.

Step 1: Create a parameter or identify the split date
Decide the date that will act as the divider for the line. This can be a static date or dynamically calculated based on your actuals. For example, you can use a fixed date such as 30 June 2025, or the latest actuals date.

- Example: SplitDate = DATE(2025,6,30)

Step 2: Create a DAX measure for the first half of the line
This measure returns the calculated value only before or up to the split date. Values beyond that will return BLANK.

P6 Remaining Units Curve Before Split =
VAR SplitDate = DATE(2025,6,30)
RETURN
IF(
    MAX('Date'[Date (month bins)]) <= SplitDate,
    CALCULATE(
        CALCULATE(sum('Resource M'[Value]),'Resource M'[Spreadsheet Field]="Actual Units"),
        FILTER(
            ALLSELECTED('Date'[Date (month bins)]),
            ISONORAFTER('Date'[Date (month bins)], MAX('Date'[Date (month bins)]), DESC)
        )
    )
    +
    CALCULATE(
        [P6 Remaining Units Curve],
        FILTER(
            ALLSELECTED('Date'[Date (month bins)]),
            ISONORAFTER('Date'[Date (month bins)], MAX('Date'[Date (month bins)]), DESC)
        )
    ),
    BLANK()
)

Step 3: Create a second DAX measure for the second half of the line
This measure returns values only for dates after the split date.

P6 Remaining Units Curve After Split =
VAR SplitDate = DATE(2025,6,30)
RETURN
IF(
    MAX('Date'[Date (month bins)]) > SplitDate,
    CALCULATE(
        CALCULATE(sum('Resource M'[Value]),'Resource M'[Spreadsheet Field]="Actual Units"),
        FILTER(
            ALLSELECTED('Date'[Date (month bins)]),
            ISONORAFTER('Date'[Date (month bins)], MAX('Date'[Date (month bins)]), DESC)
        )
    )
    +
    CALCULATE(
        [P6 Remaining Units Curve],
        FILTER(
            ALLSELECTED('Date'[Date (month bins)]),
            ISONORAFTER('Date'[Date (month bins)], MAX('Date'[Date (month bins)]), DESC)
        )
    ),
    BLANK()
)

Step 4: Add both measures to your line chart
Use these two new measures as separate series in your visual:

- Format the 'Before Split' line to be transparent or very faint.
- Format the 'After Split' line to remain yellow as desired.

Step 5: Adjust tooltip and legend
Rename or hide the legend for the first line to avoid confusion. Suppress tooltips for the transparent line if necessary using a separate tooltip page or measure-based filtering.

Step 6: Optional – dynamic split date
To dynamically calculate the split date based on data (e.g., latest actuals date), replace the static date with a dynamic variable:

- VAR SplitDate = CALCULATE(MAX('ActualsTable'[Date]), ALL('ActualsTable'))

If this solves your issue, please like and accept the solution.

[kylee_anne]

Thanks heaps!  I'll tinker with dates but this is great!

[v-dineshya]

HI @kylee_anne ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Regards,
Dinesh