Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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)
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.
Hi, this would be much easier for me if you could also paste some samples or screenshots of your data please.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
You will get more mileage if you paste that as a table.
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 |
Those do not look to be particularly easy to recreate in M. If anyone can help it is @ImkeF
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |