Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

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

Thanks in advance 

 

PBIX file

 

1 ACCEPTED SOLUTION
some_bih
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(
    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)

some_bih_0-1693692250412.png

some_bih_1-1693692313707.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

12 REPLIES 12
Stalker
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?

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!





Did I answer your question? Mark my post as a 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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






@some_bih my bad, it should be accessible now.

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],




Did I answer your question? Mark my post as a solution!

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:

ProjectTeamEstimatedHoursDate
project1SW1213-may-2025
project2SW424-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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
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(
    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)

some_bih_0-1693692250412.png

some_bih_1-1693692313707.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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!

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

 

some_bih_0-1695967676734.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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