cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Get the date when estimated value reaches 0

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)?

PBIX file

1 ACCEPTED SOLUTION
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

TableWithMeasure =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'Date'[Date] ),
"Known[X]", 'Date'[Date],
"Known[Y]", [Actual Sales]  ),
AND (
NOT ( ISBLANK ( Known[X] ) ),
NOT ( ISBLANK ( Known[Y] ) )
)
)
VAR SlopeIntercept =
LINESTX(Known, Known[Y], Known[X])
VAR Slope =
SELECTCOLUMNS(SlopeIntercept, [Slope1])
VAR Intercept =
SELECTCOLUMNS(SlopeIntercept, [Intercept])
VAR TableWithSales=
FILTER(
SUMMARIZE('Date','Date'[Date]), "@Amount",
CALCULATE( SUMX (
DISTINCT ( 'Date'[Date] ),
Intercept + Slope * 'Date'[Date] ))
),[@Amount]>0)
VAR __min_amount_table=
MINX(TableWithSales,[@Amount])
RETURN
FILTER(TableWithSales,[@Amount]=__min_amount_table)

Proud to be a Super User!

12 REPLIES 12
Frequent Visitor

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?

Super User

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.

Proud to be a Super User!

Regular Visitor

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

Super User

Hi @Stalker1 no access on the link

Proud to be a Super User!

Regular Visitor

@some_bih my bad, it should be accessible now.

Super User

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!

Regular Visitor

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.

Super User

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!

Super User

Hi @Stalker1 I will take a look this during weekend, the earliest.

Proud to be a Super User!

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

TableWithMeasure =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'Date'[Date] ),
"Known[X]", 'Date'[Date],
"Known[Y]", [Actual Sales]  ),
AND (
NOT ( ISBLANK ( Known[X] ) ),
NOT ( ISBLANK ( Known[Y] ) )
)
)
VAR SlopeIntercept =
LINESTX(Known, Known[Y], Known[X])
VAR Slope =
SELECTCOLUMNS(SlopeIntercept, [Slope1])
VAR Intercept =
SELECTCOLUMNS(SlopeIntercept, [Intercept])
VAR TableWithSales=
FILTER(
SUMMARIZE('Date','Date'[Date]), "@Amount",
CALCULATE( SUMX (
DISTINCT ( 'Date'[Date] ),
Intercept + Slope * 'Date'[Date] ))
),[@Amount]>0)
VAR __min_amount_table=
MINX(TableWithSales,[@Amount])
RETURN
FILTER(TableWithSales,[@Amount]=__min_amount_table)

Proud to be a Super User!

Frequent Visitor

Hey @some_bih , I found a more suitable alternative for my case here.

ForecastDate =
MINX(
FILTER('Calendar', [Trendline Open Issues] <= 0),
[Date])
The Trendline is just the default trend formula in powerBI, calculated over my OpenIssues table, which is linked to my Calendar table.

This returns the very first date  when trend is above 0, but it also works with all the filters that users will apply on page.

I am still curious how you can add more columns to the table in your solution, for future use.

Cheers!
Super User

Hi @Stalker great. Adding new columns in above TableWithMeasure  as shown on picture

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors