What is the equivalent of this Python script to DAX?

[davib99]

Hello!

Suppose I have a table with one date column and 300 other columns named by stocks with values being the daily return of these stocks on each date. My goal is to have a slicer of  the column "date" that will filter a specific date range and then I would like to calculate the cummulative return of all stocks in this date range. So, my ideal result table will have 3 columns: one column of date (that will be the dates in the date range selected by the slicer), another column called "stock" (with all the stocks names) and another column called "value" (with the values of the cummulative return). 

The equivalent in Python (assuming i have a df with datetime index) would be as simples as:

pd.melt(df.add(1).cumprod().add(-1), ignore_index = False)

How to write the equivalent of this sentence in DAX?

Any help would be appreciated, thanks!!

[Anonymous · Accepted solution]

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.

[davib99]

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!

[lbendlin]

usually you do the unpivoting in Power Query, not in DAX.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...