Forum Discussion
replicate Excel SLOPE calculation
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] ) )
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.
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 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] ) )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?
