Display Date where business case breaks even.Find Date in Row where numeric value first exceeds zero

[RichardJ]

Hi,

I'm working on a business case and am trying to identify the Date where the values in the Low, Medium, High and Industry Experience columns transition from being negative to zero or greater.

At the point the column value is equal to or exceeds zero I'd like to return the value of the 'Date' in the same Row.

e.g. in the Industry Experience column shown below the date returned would be 05 June 2021.

The PBIX file can be found here: https://www.dropbox.com/s/ev037p5o5n6gveb/Extract.pbix?dl=0

I've added an index to the table, and had thought of adding a column like 'Industry Experience Result' to state whether the result is positive or negative. Where the result is positive, then use the EARLIER function to find the row where the value changes from NEGATIVE to POSITIVE which would give me the row index value for the Date.

Am wondering if there is a more efficient way of getting the same result though?

In the example file provided, the desired outcome is to have 4 scenarios.

Low = Does Not Break Even

Medium = Does Not Break Even

High = Does Not Break Even

Industry Experience = 05 June 2021

Thanks for any assistance or guidance,

Richard

[HotChilli · Accepted solution]

You could improve the Line Chart a bit by adding a constant line (from the analytics menu) on 0.

If you want to write a measure to give the first date that each field is above zero, you could use this form

Measure = CALCULATE(MIN('Benefit Forecast'[Date]), 'Benefit Forecast'[Industry Experience] > 0)

[RichardJ]

@HotChilli Great suggestion about the constant line.

Thanks a lot for saving me going down the wrong (and overly complex) path with this one.

Appreciate the quick response!