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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Reference Line showing Date when forecast exceeds 50% of goal

I can usually find a similar post that is close enough to what I am looking to do and tweak it to get where I need, but I'm coming up short this time.

 

I have this visual.

NCSUAaron_1-1669130956610.png

 

 

The darker line is [Cumulative Actual], and the ligther dotted line is [Cumulative Forecast] (which is actually actuals + forecast, lying underneath the darker line so as to hav e continuous line).

 

Cumulative Actual = 
CALCULATE(
	[Cumulative Forecast],
	'Consolidated_TDP_Data'[Act/Fcst] IN { "ACTUAL" }
)



Cumulative Forecast = 
CALCULATE(
	SUM('Consolidated_TDP_Data'[Value]),
	FILTER(
		ALLSELECTED('Consolidated_TDP_Data'[Month/Year]),
		ISONORAFTER('Consolidated_TDP_Data'[Month/Year], MAX('Consolidated_TDP_Data'[Month/Year]), DESC)
	)
)

 

The reference line is the Goal (Funded).

 

Current_Funding = Sum('OY2 MIPR Breakout thru Mod 22'[Current Funding])

 

There are a few filters that can be applied to the visual to get to just the Actuals/Forecast/Goal for one project or one type of spending, and that all works fine.

 

What I'd like is to add an X-Axis Reference line at the first point where the Forecast is > 50% of the Goal/Funded line. So, in the photo above, 50% of the Goal would be $23.8M, so the refence line should shoot up from where i have my cursor, which is the first month where 50% of the goal was exceeded.

 

Thanks in advance.

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You could define a measure like

Passes halfway =
VAR Goal = [Current_funding]
RETURN
    FIRSTNONBLANK (
        'Date'[Date],
        IF ( [Cumulative forecast] > DIVIDE ( Goal, 2 ), 1 )
    )

and then use that as the value for an x-axis reference line

View solution in original post

3 REPLIES 3
johnt75
Super User
Super User

You could define a measure like

Passes halfway =
VAR Goal = [Current_funding]
RETURN
    FIRSTNONBLANK (
        'Date'[Date],
        IF ( [Cumulative forecast] > DIVIDE ( Goal, 2 ), 1 )
    )

and then use that as the value for an x-axis reference line

Anonymous
Not applicable

This did the trick.  Oddly, when I named the variable Goal like you had it, I got a, error stating "The syntax for Goal is incorrect".  I changed the variable name to Funding and that solved it.  I also added a 75% line.  Thanks for the help!  

 

NCSUAaron_0-1669218026324.png

 

 

v-xiaosun-msft
Community Support
Community Support

Hi @Anonymous ,

 

To create a vertical reference line, you need to create a "line and stacked column chart".

Create a measure.

Measure =
IF (
    'Consolidated_TDP_Data'[Cumulative Forecast]
        > RELATED ( 'OY2 MIPR Breakout thru Mod 22'[Current Funding] ) * 0.5,
    'Consolidated_TDP_Data'[Month/Year]
)

Then Put column [Month/Year] into shared axis and put [Cumulative Actual] and [Cumulative Forecast] into line values.

vxiaosunmsft_0-1669196650460.png

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you. 

 

Best Regards,
Community Support Team _ xiaosun

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.