Measure to calculate Trend line values is not correct

[Anonymous]

Hello,

I'm using the following table:  F_AMOUNTS
F_AMOUNTS  has following columns: MONTH, GROUP and AMOUNT
For every month and for every group the table countains an amount. A month is refered to by the last day of the month. 

I added following 4 columns to F_AMOUNTS:

Month Abreviation = FORMAT(F_AMOUNTS[MONTH];"MMM") 
           Month Abriviation is sorted by Month Order 
Month Order = MONTH(F_AMOUNTS[MONTH])

Year = YEAR(F_AMOUNTS[MONTH])

For every month I calculate the accumulative amount separately per group for 2019 and for 2020, using this measures:

CUMULATED AMOUNT 2019 =

CALCULATE (

    SUM ( 'F_AMOUNTS'[AMOUNT] );

    FILTER (

        ALL ( F_AMOUNTS );

        MONTH ( 'F_AMOUNTS'[MONTH] ) <= MONTH ( MAX ( 'F_AMOUNTS'[MONTH] ) )

            && YEAR ( 'F_AMOUNTS'[MONTH] ) = 2019

            && 'F_AMOUNTS'[GROUP] IN DISTINCT ( 'F_AMOUNTS'[GROUP] )

    )

)

CUMULATED AMOUNT 2020 =

CALCULATE (

    SUM ( 'F_AMOUNTS'[AMOUNT] );

    FILTER (

        ALL ( F_AMOUNTS );

        MONTH ( 'F_AMOUNTS'[MONTH] ) <= MONTH ( MAX ( 'F_AMOUNTS'[MONTH] ) )

            && YEAR ( 'F_AMOUNTS'[MONTH] ) = 2020

            && 'F_AMOUNTS'[GROUP] IN DISTINCT ( 'F_AMOUNTS'[GROUP] )

    )

)

For every month I calculate also the trend separately per group for 2019 and for 2020, using this measures:

The measures are based on the following formulas

slope α=[n∑(xy)−∑x∑y] / [n∑x2−(∑x)2]

offset β=[∑y−α∑x] / n

trendline formula yt=αx+β

TREND 2019 =

VAR Details =

    FILTER(

        SELECTCOLUMNS (

            CALCULATETABLE (

                VALUES(F_AMOUNTS[MONTH]);

                FILTER(ALLSELECTED (F_AMOUNTS);

                       YEAR(F_AMOUNTS[MONTH]) = 2019)   

            );

            "Amounts[X]";MONTH(F_AMOUNTS[MONTH]);

            "Amounts[Y]"; [CUMULATED AMOUNT 2019]

        );

        AND (NOT(ISBLANK ( Amounts[X])); NOT(ISBLANK(Amounts[Y])))

    )

VAR _n_ = COUNTROWS (Details)

VAR _Sxy_ = SUMX(Details; Amounts[X] * Amounts[Y])

VAR _Sx_ = SUMX(Details; Amounts[X])

VAR _Sy_ = SUMX(Details; Amounts[Y])

VAR _Sx2 = SUMX(Details; Amounts[X] ^ 2)

VAR _a_ = DIVIDE(_n_ * _Sxy_ - _Sx_ * _Sy_; _n_ * _Sx2 - _Sx_ ^ 2)

VAR _b_ = DIVIDE(_Sy_ - _a_ * _Sx_; _n_)

RETURN

    SUMX (

        DISTINCT(F_AMOUNTS[MONTH]);

        _a_ * MONTH(F_AMOUNTS[MONTH]) + _b_

    )

TREND 2020 =

VAR Details =

    FILTER(

        SELECTCOLUMNS (

            CALCULATETABLE (

                VALUES(F_AMOUNTS[MONTH]);

                FILTER(ALLSELECTED (F_AMOUNTS);

                       YEAR(F_AMOUNTS[MONTH]) = 2020)   

            );

            "Amounts[X]";MONTH(F_AMOUNTS[MONTH]);

            "Amounts[Y]"; [CUMULATED AMOUNT 2019]

        );

        AND (NOT(ISBLANK ( Amounts[X])); NOT(ISBLANK(Amounts[Y])))

    )

VAR _n_ = COUNTROWS (Details)

VAR _Sxy_ = SUMX(Details; Amounts[X] * Amounts[Y])

VAR _Sx_ = SUMX(Details; Amounts[X])

VAR _Sy_ = SUMX(Details; Amounts[Y])

VAR _Sx2 = SUMX(Details; Amounts[X] ^ 2)

VAR _a_ = DIVIDE(_n_ * _Sxy_ - _Sx_ * _Sy_; _n_ * _Sx2 - _Sx_ ^ 2)

VAR _b_ = DIVIDE(_Sy_ - _a_ * _Sx_; _n_)

RETURN

    SUMX (

        DISTINCT(F_AMOUNTS[MONTH]);

        _a_ * MONTH(F_AMOUNTS[MONTH]) + _b_

    )

If I compare the trend values from the measures  with the trend I get in Excel using the same values and the function =TREND(), I see that the measures TREND 2019  and TREND 2020 are not correct. They are actualy equal to the cummilated amounts.

GROUP	Year	Month Number	CUMULATED AMOUNT 2019	TREND 2019	Trend Excel
A	2019	1	240	240	171
A	2019	2	446	446	337
A	2019	3	574	574	503
A	2019	4	661	661	669
A	2019	5	777	777	834
A	2019	6	848	848	1000
A	2019	7	1051	1051	1166
A	2019	8	1205	1205	1331
A	2019	9	1442	1442	1497
A	2019	10	1681	1681	1663
A	2019	11	1924	1924	1828
A	2019	12	2144	2144	1994
B	2019	1	136	136	58
B	2019	2	263	263	240
B	2019	3	376	376	421
B	2019	4	557	557	602
B	2019	5	656	656	784
B	2019	6	940	940	965
B	2019	7	1188	1188	1146
B	2019	8	1419	1419	1328
B	2019	9	1578	1578	1509
B	2019	10	1665	1665	1690
B	2019	11	1864	1864	1872
B	2019	12	2026	2026	2053

What’s wrong with these measures, how do I correct them?

Thanks

R.W.

[v-easonf-msft]

Hi， @Anonymous 
You can try to add a another measure as below:

New Trend 2019 = CALCULATE([TREND 2019],ALL(F_AMOUNTS))

New Trend 2019=CALCULATE([TREND 2019],ALLEXCEPT(F_AMOUNTS,F_AMOUNTS[GROUP]))

Best Regards,
Community Support Team _ Eason

[Anonymous]

@v-easonf-msft 

Thanks,

But on both the measure you suggest I get an error:

R.W.

[v-easonf-msft]

Hi, @Anonymous 

Sorry for my mistake.

Please check again if the expressions I changed are useful.

New Trend 2019 = CALCULATE([TREND 2019],ALL(F_AMOUNTS))

New Trend 2019 = CALCULATE([TREND 2019],ALLEXCEPT(F_AMOUNTS,F_AMOUNTS[GROUP]))

Best Regards,
Community Support Team _ Eason

[Anonymous]

Thanks @v-easonf-msft 

I tried your measures out:

New Trend 2019 (1) = CALCULATE([TREND 2019];ALL(F_AMOUNTS))

New Trend 2019 (2) = CALCULATE([TREND 2019];ALLEXCEPT(F_AMOUNTS;F_AMOUNTS[GROUP]))

The result is not at all what I expect:

R.W.

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

The values of the variables _n_, _a_ and _b_ in your original measure show incorrect results in the current table visual.

You can separate these variables  into multiple independent measures ,which makes it easy to test whether these variables are correct in the current context.

Please check my sample file for more details.

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