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] ) )
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?
Hi,
please try to change the below part, something like,
XXXXX
VAR _condition = COUNTROWS ( FILTER ( _window, [Volume total:] <> BLANK () ) ) = 12OOOOO
VAR _condition = COUNTROWS ( FILTER ( _window, NOT([Volume total:] == BLANK ()) ) ) = 12In 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.
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:
