Forum Discussion
Convert a DAX query for use in Query Editor
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.
7 Replies
Those do not look to be particularly easy to recreate in M. If anyone can help it is ImkeF
Hi, this would be much easier for me if you could also paste some samples or screenshots of your data please.
- Below is a screen clip of sample data:
You will get more mileage if you paste that as a table.
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.
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)
