Get the date when estimated value reaches 0

[Stalker1]

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 

PBIX file

[Stalker]

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?

[some_bih]

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!

[Stalker1]

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

[some_bih]

Hi @Stalker1 no access on the link

[Stalker1]

@some_bih my bad, it should be accessible now.

[some_bih]

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

 "SystemProject", Raw_Jira_issues_or_Polarion_tasks[System project],

[Stalker1]

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.

[some_bih]

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.

[some_bih]

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

[some_bih · Accepted 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

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(

    ADDCOLUMNS(

        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)

[Stalker]

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!

[some_bih]

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