The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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:
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!
Solved! Go to Solution.
Hi,
Download my PBI file from here.
Hope this helps.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
Hi,
Download my PBI file from here.
Hope this helps.
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]
)
You are welcome.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
95 | |
80 | |
62 | |
56 |
User | Count |
---|---|
252 | |
121 | |
112 | |
81 | |
70 |