replicate Excel SLOPE calculation

[mogugu_84]

Hi Community

I need to re-create this Excel formula calculation (Slope) in Power BI. 

So basically the known x is number from 1 to 24 represent 24 months of data, from month 12, the slope calculation is known y = the unit value in row 3 for the past 12 columns and the known x = the sequence number in row 1 for the past 12 columns

as shown in the below Excel formula bar, any idea how to calculation this figure using DAX? Thanks

[Sdnf]

Thanks a lot Jihwan Kim, your solution for the slope works perfectly!

[Jihwan_Kim]

Hi,

The image is too small for me. Even if I click the image to make it larger, it is still too small.

Could you please provide your sample pbix file's link & how the expected result looks like? And then, I can try to look into it.

Thank you.

[mogugu_84]

thank you Kim. I realise i can't upload a  file or store somewhere online with link...i have pasted the actual figures as below, if this is not handy, you can use dummy numbers as well, doesnt matter. 

the key desired the outcome is to create a measure in Power BI called slope and the calculation of Slope in Sep 22 should be 35.8, as the Excel forumla spit out. and Oct 22 slope should be 20.2 ....

i have repasted the bigger photo with what's the Excel forumla looks like

if you use dummy data, as long as it match same data using the Excel "Slope" formula" calculation, it would be all perfect to adapt. Thank you

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
Month	Oct.21	Nov.21	Dec.21	Jan.22	Feb.22	Mar.22	Apr.22	May.22	Jun.22	Jul.22	Aug.22	Sep.22	Oct.22	Nov.22	Dec.22	Jan.23	Feb.23	Mar.23	Apr.23	May.23	Jun.23	Jul.23	Aug.23	Sep.23
Volume	2,828	3,247	3,313	2,775	2,912	3,299	2,925	3,149	3,193	3,172	3,524	3,402	3,114	3,373	3,560	3,032	3,164	3,384	3,227	3,719	3,323	3,578	3,669	3,490

[Jihwan_Kim · Accepted solution]

Hi, Please check the below picture and the attached pbix file.

Volume total: = 
SUM( Data[Volume] )

WINDOW function (DAX) - DAX | Microsoft Learn

Slope: = 
VAR _calendar =
    ALL ( 'Calendar'[Year-Month], 'Calendar'[Year-Month sort] )
VAR _window =
    WINDOW (
        -11,
        REL,
        0,
        REL,
        _calendar,
        ORDERBY ( 'Calendar'[Year-Month sort], ASC )
    )
VAR _condition =
    COUNTROWS ( FILTER ( _window, [Volume total:] <> BLANK () ) ) = 12
VAR _t =
    ADDCOLUMNS (
        _window,
        "@x", RANK ( SKIP, _calendar, ORDERBY ( 'Calendar'[Year-Month sort], ASC ) ),
        "@y", [Volume total:]
    )
VAR _linestx =
    LINESTX ( _t, [@y], [@x] )
RETURN
    IF ( _condition, MAXX ( _linestx, [Slope1] ) )

[Sdnf]

Thanks for this solution Jihwan it works perfectly but I found an issue when the volume data is = 0 in any month included in the 12 previous, slope is not calculated. Look this snapshot: Maybe DAX slope formula needs some adjustements to avoid this issue?

[Jihwan_Kim]

Hi,

please try to change the below part, something like,

XXXXX

VAR _condition =
    COUNTROWS ( FILTER ( _window, [Volume total:] <> BLANK () ) ) = 12

OOOOO

VAR _condition =
    COUNTROWS ( FILTER ( _window, NOT([Volume total:] == BLANK ()) ) ) = 12

In current measure, the condition checks the blank value, including zero.

In the fixed measure, the condition checks the real blank value, excluding zero.

Please check whether this works.

Thank you.

[Sdnf]

Thanks Jihwan but I'm afraid this solution doesn't work, now the slope figures are wrong and includes also months without volume figures despite those months are not selected in the months filter:

[Jihwan_Kim]

Hi,

Please try to use LINESTX DAX function.

LINESTX function (DAX) - DAX | Microsoft Learn

[Sdnf]

Hi,

I'd also need exactly how to recreate this slope formula in power BI, could you please write the complete DAX formula for this request?:

 I need to re-create this Excel formula calculation (Slope) in Power BI. 

So basically the known x is number from 1 to 24 represent 24 months of data, from month 12, the slope calculation is known y = the unit value in row 3 for the past 12 columns and the known x = the sequence number in row 1 for the past 12 columns

as shown in the below Excel formula bar, any idea how to calculation this figure using DAX? Thanks