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] ) )

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
1 year ago

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

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: