Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi! I've been struggling with this for a while, but I just can't figure it out.
I need to obtain the date when a measure reaches 0. I've attached a PBIX file with a simple linear regression (similar to my real model), How would I return the calendar date when the Estimated sales value reaches 0 (could be years into the future)?
Thanks in advance
Solved! Go to Solution.
HI @Stalker1 I created Table with Measure to filter which date is date with minimal sales for date just before negative amount as calculation in this example change from positive to negative amount.
Kudos appreciated / accept solution
Proud to be a Super User!
Hi @some_bih , thank you for the reply.
This seems to work, however It would need some adjustments for my real model. Meaning I have data for multiple projects in one place, so filtering would need to work. People with access to one project will have the whole report filtered for that project only.
Header example would be {Project, Team, Date, Amount}
Can your solution be transposed into a measure that returns the date of the minimum value, while also being responsive to different filters applied on page?
Hi @Stalker I would say there are some limitations for "ultimate solution as measure" in this particular case.
I will try to explain to you: I used your code to "create" table, nothing too much coplex, even your base function is LINESTX, statistical function and type iterator. To use this as measure CALCULATE should be called an similar which is not easy for this function.
Still, this is not so so complex as usage another function in your base scenario ALLSELECTED. Creating another measure from output of this solution is not best practice, only include as visual.
Based on this I suggest you to use my solution as matrix.
What you can do is to include some columns into part ADDCOLUMNS(SUMMARIZE like Project, Team and try would it be better than this solution. Otherwise, think how to rewrite LINESTX in another way to avoid ALLSELECTED.
Did I answer your question? Kudos appreciated / accept solution!
Proud to be a Super User!
Hi @some_bih ,
I tried adding more columns in the ADDCOLUMNS part, but the result was wrong. I'm calculating the trend based on the last 6 months of data, which is the OpenIssues table, and the results are the same as the default PowerBI trendline.
I don't really know when to use Calendar[date] or OpenIssues[datekey] or how to add the relevant columns from OpenIssues, and for the calculation to work.
Does that make sense?
I have included my simplified model I work with, please take a look and advise. pbix
Thank you
Hi @Stalker1 no access on the link
Proud to be a Super User!
Hi @Stalker1
I got your report. I see in table "OpenIssues" that you added some columns, so you known how to add column, example part below.
I do not understand what do you want, add some other columns - just use comma, and next is expresssion (measure, column...). Hope this help
Proud to be a Super User!
Hi @some_bih , I want to add more columns to the calculated "EstimatedTable", to have the date for the minimum "EstimatedHours" calculated per project or team. People should be able to filter per project and view the date when EstimatedHours gets to 0 (or close).
Example:
Project | Team | EstimatedHours | Date |
project1 | SW | 12 | 13-may-2025 |
project2 | SW | 4 | 24-Aug-2025 |
If it helps, the base requirement of the KPI I'm struggling to implement is: "Does the value of EstimatedHours (or hours for open issues) reach 0 before a certain given date? If yes, it's green, otherwise red."
This needs to be calculated per project and team.
These trendline values can be seen on the first visual.
Hi @Stalker1
The point is "EstimatedHours gets to 0 ", right?
I will take a look your model (as I need to grasp it) and let you know.
Proud to be a Super User!
Hi @Stalker1 I will take a look this during weekend, the earliest.
Proud to be a Super User!
HI @Stalker1 I created Table with Measure to filter which date is date with minimal sales for date just before negative amount as calculation in this example change from positive to negative amount.
Kudos appreciated / accept solution
Proud to be a Super User!
Hey @some_bih , I found a more suitable alternative for my case here.
Hi @Stalker great. Adding new columns in above TableWithMeasure as shown on picture
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
76 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
106 | |
77 | |
66 | |
65 |