Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Python script to DAX

Hi, I am trying to convert the below python script to DAX Calculated column:           import numpy as np dataset["OBV"] = np.where(dataset['close'] > dataset['close'].shift(1), dataset['volum...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    for your requirement, you need to create two column to get your requirement:

    judgecolumn = var _yesterdaydate=CALCULATE(MAX('Table'[As of Date]),FILTER('Table','Table'[As of Date]<EARLIER('Table'[As of Date])))
var _yesterdayclose=CALCULATE(SUM('Table'[Close]),FILTER('Table','Table'[As of Date] = _yesterdaydate))
return

DIVIDE('Table'[Close]-_yesterdayclose,ABS('Table'[Close]-_yesterdayclose)) *'Table'[Volume]
    OBV = 

SUMX(FILTER('Table','Table'[As of Date]<=EARLIER('Table'[As of Date])),[judgecolumn])

     

    Result:

    For first date of your sample data, there is no yesterday OBV, so I have give a fixed value

    OBV = 

SUMX(FILTER('Table','Table'[As of Date]<=EARLIER('Table'[As of Date])),[judgecolumn])+287025018164

     

    here is sample pbix file, please try it.

     

     

    Regards,

    Lin

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , what is expected output and logic.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     Hello amitchandak 
    Thanks for your response. 

     

    Sample Data: Sample Data 

    Input Columns: As of Date, Close, Volume

    Output Column: OBV


    Business Logic:

    If today's close is greater than yesterday's close then:
             OBV = Yesterday’s OBV + Today’s Volume

    If today’s close is less than yesterday’s close then:
             OBV = Yesterday’s OBV – Today’s Volume 

    If today’s close is equal to yesterday’s close then:
             OBV = Yesterday’s OBV

     

     

    Execution using Python:

     

     

     

     

     

     

     

    import numpy as np
dataset["OBV"] = np.where(dataset['close'] > dataset['close'].shift(1), dataset['volume']  ,
np.where(dataset['close'] < dataset['close'].shift(1), -dataset['volume'], 0)).cumsum()

     

     

     

     

     

     

     

     

    I am trying to execute the same using DAX. 

     

    Let me know if you have questions!

     

    Thanks,

    SV

     

    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      hi Anonymous 

      for your requirement, you need to create two column to get your requirement:

      judgecolumn = var _yesterdaydate=CALCULATE(MAX('Table'[As of Date]),FILTER('Table','Table'[As of Date]<EARLIER('Table'[As of Date])))
var _yesterdayclose=CALCULATE(SUM('Table'[Close]),FILTER('Table','Table'[As of Date] = _yesterdaydate))
return

DIVIDE('Table'[Close]-_yesterdayclose,ABS('Table'[Close]-_yesterdayclose)) *'Table'[Volume]
      OBV = 

SUMX(FILTER('Table','Table'[As of Date]<=EARLIER('Table'[As of Date])),[judgecolumn])

       

      Result:

      For first date of your sample data, there is no yesterday OBV, so I have give a fixed value

      OBV = 

SUMX(FILTER('Table','Table'[As of Date]<=EARLIER('Table'[As of Date])),[judgecolumn])+287025018164

       

      here is sample pbix file, please try it.

       

       

      Regards,

      Lin

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi i need to convert this python script into dax any idea?