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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MKPartner
Helper I
Helper I

Trend line (Linear regresion) not working for a graph with "Year" axis

Hello, 

 

I'm using below DAX code to create a trend line:

 

%8D closed on time 12M TREND = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'COQ Calendar'[WC_Month] ),
            "Known[X]", 'COQ Calendar'[WC_Month],
            "Known[Y]", [%8D closed on time12M_v2]
        ),
        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] ^ 2 )
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 ^ 2
    )
VAR Intercept =
    Average_Y - Slope * Average_X

VAR MaxDate =
    MAX ( 'DATE_DUP'[WC_Month] )
VAR MinDate =
    DATE ( YEAR ( MaxDate ), MONTH ( MaxDate ) - 12, DAY ( MaxDate ) )
    
RETURN
    CALCULATE(    
            SUMX (
                DISTINCT ( 'COQ Calendar'[WC_Month] ),
                Intercept + Slope * 'COQ Calendar'[WC_Month]
            ),
        FILTER ( 'COQ Calendar', 'COQ Calendar'[WC_Month] > MinDate && 'COQ Calendar'[WC_Month] <= MaxDate )
    )

 

It is working for graphs where X axis is created as month & year like below: 

 

Error.JPG

 

I'm trying to modify the same code to use it for graph where X axis is built only as Years but it is not working: 

 

AVG 8D closed on time TREND = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'COQ Calendar'[WC_Month] ),
            "Known[X]", 'COQ Calendar'[WC_Month],
            "Known[Y]", SUMX('8D Log','8D Log'[Over 10 Days])/COUNTROWS('8D Log')
        ),
        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] ^ 2 )
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 ^ 2
    )
VAR Intercept =
    Average_Y - Slope * Average_X

VAR MaxDate =
    MAX ( 'DATE_DUP'[WC_Month] )
VAR MinDate =
    DATE ( YEAR ( MaxDate ), MONTH ( MaxDate ) - 12, DAY ( MaxDate ) )
    
RETURN
    CALCULATE (
        SUMX (
        DISTINCT ( 'COQ Calendar'[WC_Month] ),
        Intercept + Slope * 'COQ Calendar'[WC_Month]
        ),
        FILTER ( 'COQ Calendar', 'COQ Calendar'[WC_Month] > MinDate && 'COQ Calendar'[WC_Month] <= MaxDate )
    )

 

Result of above code. AS you can see Trend results (orange line) is much higher than KPI result: 

 

error1.JPG

 

Please help me modify this DAX code to get proper results on graph. 

0 REPLIES 0

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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