New to PowerBI: How to create trendline for non-conformances

[edidonato]

Good Morning All,

I'm new to PowerBI and Excel as a logical tool. I've always used this Microsoft product as a statistical tool.

My first task at my new job is to create Quality KPI metrics. I'm stuck on creating a trendline for:

1)overdue (30+days) non conformance that are open. My current powerBI build is using a splicer that slides to get the answer (example Jan2019-May2021, Jan2019-April2021, etc). What logical test can I apply that the formula is more dynamic?

2) Non-conformance per month?

Any help from the community is greatly appricated. 

[v-yingjl · Accepted solution]

Hi @edidonato ,

Not certian what is your data source look like and the expected result but basically the calculation logic of the trendline would be like this:

Linear regression = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Table'[Date] ),
            "Known[X]", 'Table'[Date],
            "Known[Y]", CALCULATE ( SUM ( 'Table'[Sales] ) )
        ),
        AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
    )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] * Known[X] )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X * Sum_X
    )
VAR Intercept = Average_Y - Slope * Average_X
RETURN
    SUMX ( DISTINCT ( 'Table'[Date] ), Intercept + Slope * 'Table'[Date] )

You can also refer this simliar issue: Trend line values 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.