Forum Discussion
What is the equivalent of this Python script to DAX?
Thank you for your prompt reply! Your answer provided me with a great idea！
Hi davib99Based on your needs, I have created the following table.
In the Power Query Editor, select the table that contains the dates and daily returns of the stocks. Select all the stock columns (e.g., StockA, StockB, StockC). You can select multiple columns by holding down the Ctrl key. After selecting all the stock columns, right-click any of the selected columns. Choose the 'Unpivot Columns' option.
Then create the following measure:
Cumulative Return = VAR CurrentDate = MAX('StockReturns'[Date]) VAR CurrentStock = SELECTEDVALUE('StockReturns'[Stock]) RETURN CALCULATE( PRODUCTX( FILTER( ALL('StockReturns'), 'StockReturns'[Date] <= CurrentDate && 'StockReturns'[Stock] = CurrentStock ), 1 + 'StockReturns'[Value] ) - 1 )Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your prompt reply! Your answer provided me with a great idea！
Hi davib99
Based on your needs, I have created the following table.
In the Power Query Editor, select the table that contains the dates and daily returns of the stocks. Select all the stock columns (e.g., StockA, StockB, StockC). You can select multiple columns by holding down the Ctrl key. After selecting all the stock columns, right-click any of the selected columns. Choose the 'Unpivot Columns' option.
Then create the following measure:
Cumulative Return =
VAR CurrentDate = MAX('StockReturns'[Date])
VAR CurrentStock = SELECTEDVALUE('StockReturns'[Stock])
RETURN
CALCULATE(
PRODUCTX(
FILTER(
ALL('StockReturns'),
'StockReturns'[Date] <= CurrentDate &&
'StockReturns'[Stock] = CurrentStock
),
1 + 'StockReturns'[Value]
) - 1
)
Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Jayleny!
Firstly, thanks for your reply! You helped a lot. If I can just ask you one more thing, your solution almosts works but when I filter on the Date Slicer, for example I selected a date greater than the minimum possible date, it doesn't recalculate the table based on that. Is there a way to deal with that?
Again, thank you very much for your help!
