topic Re: Need help creating new column in DAX Commands and Tips

Need help creating new column

praveenjujare — Wed, 13 Nov 2024 13:41:34 GMT

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

SWITCH(

TRUE(),

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

)

Re: Need help creating new column

123abc — Wed, 13 Nov 2024 15:18:55 GMT

please shere more detail or pbix file.

Re: Need help creating new column

Anonymous — Thu, 14 Nov 2024 02:38:48 GMT

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

Re: Need help creating new column

praveenjujare — Thu, 14 Nov 2024 05:30:12 GMT

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

Re: Need help creating new column

Anonymous — Thu, 14 Nov 2024 07:05:44 GMT

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

Re: Need help creating new column

praveenjujare — Thu, 14 Nov 2024 08:04:05 GMT

Thanks sir