I have enclosed two files: Data and Test (PBi file). Data file has input fields and desired output.
Challenge: The dates are not in sequence and there are also product categories. Hence, the calculation done to compute the row differences has been done by me using an index col and by introducing columns rather than a measure.
Request: Need help in developing a measures cal for calculating “Value1” and “EMA”.
I have developed both column calculations and one measures cal but it does not address a dynamic scenario
Will request your helping using “Time intelligence calculations” using previousday(), dateadd etc
Welcome to the forum. This is a perfect example of why a “Date Table” is needed. In order to do correct Time Intelligence measures you will need a date Table. Sam has lots of great content on how to setup a proper Date Table. I have also added a document with some M code that you will need once you view his video. Let me know If you need any other guidance once you have reviewed this material.
Why do you need the “Diff-col-method2” calculated column? Looks like your other calculated column using the index is giving you the correct answer when compared to your Excel Sheet. Need to be much clearer of what you want to accomplish, and if you know the given correct result, share that as well.
I think I have come up with a solution using what you have provided. I have attached the PBIX with my changes. Here is a snapshot of what it looks like:
If i manage to add a date field using the workday column then i can use concepts such as PreviousDay etc. However, iam not able to do this and need help
Value 2 : replicates the EMA calculation done in the below link
I’ll have a look into this, but probably won’t be until after the holiday weekend. In the mean time, have a look at this resource pack from Sam on a Stock Market Dashboard. I’m sure you can follow the logic in this resource and come up with a solution. Let me know what you come up with.
I want to thank you to take efforts and help me with the dashboard. I have analysed it and have few questions /clarifications:
a) Weekday calculation: In the data set i have given, the dates where the prices are appearing are for days lesser than the weekday. Hence i have introduced a column - “Closing price date” in the Date table. Hope this logic is correct. There are approx 31 days variations between ‘is Week day’ col and ‘closing price date’ col
b) The calculation of 30MA and 90 MA is on the basis of calendar month period not exact days. Hence i have introduced a col (N_Key) in closing prices table and working ‘mov30days’ and ‘mov7days’ which is based on Numerator /Denominator (distinct count) calculation. Is there a way we can correct 30MA and 90MA to introduce the exact days rather than calendar days. In page 2 dashboard, i have given the comparison of the two calculations.
c) Closing price for given date: All the calculations given in the dashboard are based on ‘Last pricing date’. This is good. However, some advanced calculations that i wish to carry out requires subtracting one row to the other as given ‘Value1’ col and ‘EMA’ col of the data file. This will require using ‘Previous day’ formula etc. Currently previous day is taking ‘last price’ not price on the date of calculation.Can Time intelligence be applied like the way ‘Last pricing date’ has been calculated. If you can help solve this, it would solve a major problem for me.
d) 30dayago price, 7 dayago price etc: Page2: why are the same values appearing on all the rows. I think if we solve ‘c’ above, we can solve ‘d’
e) lastly: in “Stock price” measure, “Codes[Stock Codes] =” has no implication and consequence as it is anyway for filtered info. Please confirm.
A: The Weekday calculation only includes days from Monday-Friday I looked through the data and all of the dates where there is closing prices and volume data are all on Weekedays. This is why I marked this calculation as TRUE as a filter on the page so that only data will show that is on a Weekday. There really is much of a need for you new column you added. I you wish to add it, you would need to apply the same logic as I did in the Pricing Data Exists column.
B: You are correct that the measure calculation of the moving averages is by calendar month, but when you apply the Weekday is TRUE as a filter on this page, it will calculate those averages based only on Weekdays. I also have the Pricing Data Exists column as a page filter as well.
C: I will need to work on solving this issue, because it doesn’t currently work the way you want it to.
D: The 30 Days Ago & 7 Days Ago Measures are showing same numbers because it is going off the MAX Date in the date range on the page. You are correct in thinking that once we solve C, D can be answered with similar logic.
E: The way this is currently designed you would still want whatever Stock you want to be the default if it is not filtered, just in case there ever is a situation where it might not be filtered. Don’t get hung up on this one, leave it as is.
Here is what I have completed so far. I figured out Value 1, 7 Days Ago & 30 Days Ago Measures. Still working on getting the 30 & 90 Moving Averages. I put each of those measures in the Closing Prices table.
I got a bit creative on adding the Index column. I saw a similar post to this that @Nick_M came up with this solution. If you go into the M code you will se how I grouped the Index number by stock symbol.
In the link below, the working on EMA has been solved.
Request your help you solve this last leg. Looking forward to your solution to help us calculating the Moving average and EMA. The outcome similar to EMA in the last col of the enclosed sheet.
Let me know if this is what you are looking for with the 7, 30 & 90 Day Moving Averages before I move on to tackle the EMA. EDNA- Solution Except EMA.pbix (721.7 KB)
@Jarvis, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
Iam very much comfortable with the entire calculation. There is only one element that i could not perhaps find in the solution which is “Calculation of EMA”. That was the last col in my excel sheet model.
I did try using the last PowerBI output with the below measures:
prevday30MA =
CALCULATE(
‘Moving Averages’[mov30days JRM],
PREVIOUSDAY(Dates[Date]))
EMA =
(0.0006*(‘Moving Averages’[mov30days JRM]-prevday30MA)) + [prevday30MA]
Iam not getting the output for the above. Question: Would the previous day formula above take the right date i.e filtered on the exact dates for which the prices area available.
With the above calculations in the model my solution would be complete.
II don’t believe that you can use PREVIOUSDAY that way you have things setup in your model, especially when there is not data for every day. I will have another go at this on Monday (Located in the U.S.).
I have manage to solve one part of the problem on previous day averages in column mdata1.
Despite fair degree of research put in by me, couldnt solve the “Loop” problem in calculating “EMA” column in the excel sheet. EMA takes value from the preceding cell. The first row is the average of x no of days, rest are computed using the same column and previous value. So a circular ref is getting formed
This is the closest I can get to coming up with a solution with the information you have provided. Let me know if this works for you. If not, you need to provide an Excel sheet with the data that is in the model with the expected result. Here is the link that I tried to get as close as I could to replicating how they solved the EMA:
