Measure to harvest date when another measure equals a particular value

[EricPeabody]

Howdy! I have a visual that shows supply versus demand. I have a measure called "Future Surplus" which is the difference between supply and demand. I am trying to create a measure that dynamically reports the year that the Future Surplus measure equals zero (the breakeven date) so that I can use it to drive the X-axis Constant Line. I have used a hard-coded x-axis constant in the screenshot below to simulate what I am trying to achieve. This seems like it should be a fairly simple measure to create but after trying a dozen or so options I have not yet succeeded. Here is a vastly simplified model that has all the relevant relationships and measures: https://app.box.com/s/3wbh70ykqha2axmqukpigwbfyjk3eplz

My first impulse was to iterate through the date table like this:

CALCULATE(

     MAXX(LookupDate,LookupDate[Fiscal Year]),

     [Future Surplus]>0

)

I think I need to create a virtual table and then check for the max Fiscal Year, but I am not quite getting there. I would appreciate any help. Thanks!

[parry2k]

@EricPeabody try this measure:

Max Value =

CALCULATE(
     MAXX(ALLSELECTED(LookupDate[Fiscal Year]),
     [Future Surplus]
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[EricPeabody]

Thanks for the reply @parry2k. That measure returns 0. Somehow I need a way to pull that one date where the Future Surplus measure equals zero. I edited my post above to include a vastly simplified PBIX file with all the relevant relationships and measures: https://app.box.com/s/3wbh70ykqha2axmqukpigwbfyjk3eplz

[Ashish_Mathur · Accepted solution]

Hi,

Download my PBI file from here.

Hope this helps.

[EricPeabody]

Thank you @Ashish_Mathur that was totally the ticket! That totally makes sense. I was trying to do the filter the wrong way around. For those that don't want to DL the file, here is the DAX:

Breakeven Year =
MAXX (
    FILTER (
        ALL ( LookupDate[Fiscal Year] ),
        [Future Surplus] > 0
    ),
    LookupDate[Fiscal Year]
)

[Ashish_Mathur]

You are welcome.