Forum Discussion

mogugu_84's avatar
mogugu_84
Helper I
1 year ago
Solved

replicate Excel SLOPE calculation

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 calcul...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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's avatar
Sdnf
Regular Visitor
1 year ago

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