Convert a DAX query for use in Query Editor

[abraatz]

I would like to be able to recreate the steps below for use in Query Editor using M, rather than DAX, so it can be recreated and run against numerous queries, before appending them together.

The initial query returns several columns but only two are needed to create the necessary calculated columns (Date in descending order, Adj Close). I also create an added column in Query Editor to assign a constant Name to all rows. I use the following steps to get to the final value that I would like by creating additional columns.

Column 1 - PrevDate - there are gaps in dates so need to use a Max function to find max date less than current row date

PrevDate = CALCULATE(MAX(Query1[Date]), (FILTER(Query1,EARLIER(Query1[Date])>Query1[Date])))

Column 2 - PreAdjClose

PreAdjClose = CALCULATE(SUM(Query1[Adj Close]), (FILTER(Query1, EARLIER(Query1[Ticker])=Query1[Ticker] && EARLIER(Query1[PrevDate])=Query1[Date])))

Column 3 - DayReturn - need to apply blank to final row, since it will return infinity otherwise

DayReturn = IF(ISBLANK([PrevDate]), Blank(), Query1[Adj Close] / Query1[PreAdjClose] - 1)

If it is possible to skip the PrevDate column and just retrieve the value from the next row Adj Close (since descending order), I do not necessarily need that column.

Any help would be greatly appreciated.

[dkay84_PowerBI]

Also, please note that if you are going to calculate the cumulative return over a given period, you can leave the -1 off of the return equation and just do a multiplication of all the daily returns (I think PRODUCTX is the function to use here.  See: https://msdn.microsoft.com/en-us/library/dn802544.aspx)

[dkay84_PowerBI]

From reading your measure names, I assume you are tracking the adj. close price of a stock and trying to calculate the daily return based on the previous day's adj. close vs. the current day's.  In other words, you want adjusted close in row 2 divided by adjusted close in row 1.

I think the easiest way to do this is to create a copy of your table, then add an index to Table 1 starting from 0, and an index for Table 2 starting at 1.  Then merge the two tables by index and you will get a row offset for Adjusted Close.  Then a simple calc column can calculate the daily return.

[ImkeF]

Hi, this would be much easier for me if you could also paste some samples or screenshots of your data please.

[abraatz]

Below is a screen clip of sample data:

 

[Greg_Deckler]

You will get more mileage if you paste that as a table.

[abraatz]

Date  Open High Low Close Volume AdjClose Ticker PrevDate PreAdjClose DayReturn 

Friday, April 28, 2017	160.5	160.589996	159.699997	160.289993	4154100	158.739298	IBM	Thursday, April 27, 2017	158.769022	-0.000187215362452808
Thursday, April 27, 2017	160.289993	160.479996	159.630005	160.320007	4122600	158.769022	IBM	Wednesday, April 26, 2017	158.511527	0.00162445599303318
Wednesday, April 26, 2017	160.529999	161.100006	159.889999	160.059998	4327800	158.511527	IBM	Tuesday, April 25, 2017	158.838337	-0.00205750076569988
Tuesday, April 25, 2017	161.779999	162.039993	160.380005	160.389999	4860400	158.838337	IBM	Monday, April 24, 2017	159.194855	-0.00223950704939546
Monday, April 24, 2017	161.289993	161.570007	160.419998	160.75	5320600	159.194855	IBM	Friday, April 21, 2017	158.828439	0.00230699238944232
Friday, April 21, 2017	162.050003	162.110001	160.380005	160.380005	5607800	158.828439	IBM	Thursday, April 20, 2017	160.729862	-0.0118299298981541

[Greg_Deckler]

Those do not look to be particularly easy to recreate in M. If anyone can help it is @ImkeF