Forum Discussion

praveenjujare's avatar
praveenjujare
Icon for Helper I rankHelper I
1 year ago
Solved

Need help creating new column

 Share adjustment price =

 

VAR _INDEX = MAX('Investment'[Index])  

 

VAR _INDEX_1 = _INDEX - 1  

 

VAR a = SUMX('Investment', 'Investment'[Qty])  

 

VAR b = SUMX('Investment', 'Investment'[Price])  

 

VAR c = [Closing Share]  

 

VAR d =

 

    CALCULATE (

 

        [Closing Share],

 

        'Investment'[Index] = _INDEX_1  

 

    )

 

VAR e = (b * a) / c  

 

RETURN

 

    RETURN

 

    SWITCH(

 

        TRUE(),

 

'Investment', 'Investment'[Transaction Type] = "Shares Adjustment", e,

 

        0

 

    )

Unable to get correct values
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi praveenjujare ，

     

    Pease try:

    Column = 
IF(
    'Investment'[Transaction Type] = "Shares Adjustment",
    VAR PreviousRowPrice = CALCULATE(
        MAX('Investment'[Price]),
        FILTER(
            'Investment',
            'Investment'[Index] = EARLIER('Investment'[Index]) - 1
        )
    )
    VAR PreviousRowClosingShare = CALCULATE(
        MAX('Investment'[Closing Share]),
        FILTER(
            'Investment',
            'Investment'[Index] = EARLIER('Investment'[Index]) - 1
        )
    )
    RETURN
        (PreviousRowPrice * PreviousRowClosingShare) / 'Investment'[Closing Share],
    BLANK()
)

     

    Best Regards,

    Bof

     

5 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    1 year ago

    please shere more detail or pbix file.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi praveenjujare ,

     

    Based on the DAX you provided, I’m not able to fully understand the calculation logic for this new column. Could you please describe the calculation logic for this column in words?

     

    Best Regards,

    Bof

    • praveenjujare's avatar
      praveenjujare
      Icon for Helper I rankHelper I
      1 year ago

      When transaction type = share adjustment then (previous row of price * previous row of closing share )/ present row of closing share

       

      Example:   (65.4 * 273000)/2730000

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi praveenjujare ，

         

        Pease try:

        Column = 
IF(
    'Investment'[Transaction Type] = "Shares Adjustment",
    VAR PreviousRowPrice = CALCULATE(
        MAX('Investment'[Price]),
        FILTER(
            'Investment',
            'Investment'[Index] = EARLIER('Investment'[Index]) - 1
        )
    )
    VAR PreviousRowClosingShare = CALCULATE(
        MAX('Investment'[Closing Share]),
        FILTER(
            'Investment',
            'Investment'[Index] = EARLIER('Investment'[Index]) - 1
        )
    )
    RETURN
        (PreviousRowPrice * PreviousRowClosingShare) / 'Investment'[Closing Share],
    BLANK()
)

         

        Best Regards,

        Bof