topic Re: Reference Line showing Date when forecast exceeds 50% of goal in DAX Commands and Tips

Reference Line showing Date when forecast exceeds 50% of goal

Anonymous — Tue, 22 Nov 2022 15:30:09 GMT

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.

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.

Re: Reference Line showing Date when forecast exceeds 50% of goal

v-xiaosun-msft — Wed, 23 Nov 2022 09:52:24 GMT

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.

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.

Re: Reference Line showing Date when forecast exceeds 50% of goal

johnt75 — Wed, 23 Nov 2022 10:47:32 GMT

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

Re: Reference Line showing Date when forecast exceeds 50% of goal

Anonymous — Wed, 23 Nov 2022 15:40:36 GMT

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!