Hi,
While viewing last 7 days/30 days performance (irrespective of top or worst), if 7th day or 30th day fall on weekend, dashboard appears blank in line with dax formula. How come we make it dynamic like instead of -7 or -30 it should be like -7 or -30 weekdays, so that on the one hand dashboard doesn’t turn blank and on the other hand give true result in terms of weekdays.
Regards,
AM
You can create calculated column in the table which shows the days. In the visual level just exclude the weekend days. Then it should give you the proper result.
Thanks for your response.
Let me introduce little twist with an example:
Regards,
AM
Hi,
check out below video, it shows how to deal with weekend days for exchange rates.
Other options are, when you have a table in PQ with blanks for weekenddays to apply fill down to fill null values with the friday rate.
Paul
If I am getting you correctly, you need eliminate the weekend days first then do the 7 days calculation. Can you clarify this? you have mentioned 19 October , But 19 October itself Saturday. Is that a typo?
you can also check below links
I’m going to come at this another way. The attached excel file connects to Yahoo Finance. I think it’s bringing in S&P 500 data.
Here’s what it is doing:
Create a Calendar table that has an Index (Called Cumulative Day in this example.
Add an index column to your Fact table. Since this is just one Index can do that that directly. If you have multiple stocks/indexes you would need to group first and then add an index column. Can show you how to do that if needed.
Load all that to your data model, and related the Calendar table to the Fact table on the newly created index column
Then can create these measures:
Total Close:=SUM( FactDailyData[Close*] )
50 Day Average :=
VAR __Length = 50
VAR __CurrentIndex =
MAX ( FactDailyData[Index] )
VAR __PrevIndex = __CurrentIndex - __Length
RETURN
IF (
__CurrentIndex >= __Length,
AVERAGEX (
FILTER (
ALL ( FactDailyData ),
__CurrentIndex >= FactDailyData[Index]
&& __PrevIndex < FactDailyData[Index]
),
FactDailyData[Close*]
)
)
So in this example we have a 50 day moving average. We will not get an average till we have 50 days of data, not just 50 calendar days. But can change the 50 to whatever length.
Hi Nick,
Yes, as my data has multiple stocks, pls do let me know the steps needed to create grouping based on date.
I have added Cumulative Day column in date table.
@AamerMalik
Do you have some sample data? Would need it in excel/text/csv since will be doing this in PQ.
But high level:
@AamerMalik-
So I went ahead and put a sample together. The more I thought about it, the more I didnt like my initial solution. While it would work, I really didnt like this part.
FILTER ( ALL ( FactDailyData ),
But first things first, getting the index column for each stock. In the attached PBIX file you will see a few tables and couple functions in PQ. Focusing on the “FactPerformanceIndexManual” for the time being. There is a list of stocks (it will reference a different table where I already have the Index, so I need to remove that to get a “clean slate”)
We then sorted by Symbol and then by Date Ascending. Then group the rows by Symbol and aggregate by all data. Now, we need to add something to the end of generated M code:
This will tell PQ to keep the order that we had set eariler for each grouping. So we have:
Now add a custom column with the following to get an Index for each sub table:
Table.AddIndexColumn(
[AllRows],"Index",1,1)
Its the same code that would be generated from the Add Column–>Index Column toolbar, with the exception we add in our [AllRows] as the table, so that PQ will restart the count at each table. Remove all the other columns except this last one and set data types and you are all set:
Load that, along with your calendar table and should have Dimension table for your Tickers as well. Data model looks like:
With Dates on rows from our DimCalendar Table, and Symbol (from DimTickers) as a slicer (or could be whatever) we can use the following function. I put comments in the code.
"X" Day Average =
Var _Length=10
/*This gives the total count of rows for each stock up until the current date
want to be sure we have enough data points to generate a correct MA*/
Var _PerformanceCount=
CALCULATE(
COUNTROWS( FactPerformance ),
FILTER(
ALL( DimCalendar),
MAX(DimCalendar[Date]) >= DimCalendar[Date]
)
)
/*Since we cannot use simply 10 days, we need to figure out what the date is of the
10th day.
-We want to lookup the Date of the 10th day back in the Fact table
-In that table, we go to the Index column and want to find where index is equal to the
length +1. We add one so that we count the current day
-Then we want to be sure we have the correct symbol
-This then gives us what the date was of the 10th day back*/
Var _BeginDate =
LOOKUPVALUE(
FactPerformance[Date]
,FactPerformance[Index],_PerformanceCount - _Length +1
,FactPerformance[Symbol], SELECTEDVALUE(DimTickers[Symbol])
)
/*What the current date is in the currnt filter context*/
VAR _CurrentDate= MAX( DimCalendar[Date])
RETURN
IF(
_PerformanceCount >= _Length --This checks to be sure we have enough data points
&&
NOT ISBLANK([Total Close]), --This checks to be sure we have a close on that date
CALCULATE( --Calculate is the only thing that can change the filter context
[Avg Close], --Just a simple Average(Fact[Close])
DATESBETWEEN( --DatesBetween returns a table of dates from start and end date
DimCalendar[Date] --Dates Columns
,_BeginDate --StartDate is our begin date variable from above
,_CurrentDate --EndDate is just the current date in the current filter context
)
)
)
This is just a “bonus”, but there’s a function in the file that will go out to yahoo finance and get the historical data for a list of tickers:
(Ticker) =>
let
Source =
Web.Page(
Web.Contents(
"https://finance.yahoo.com/quote/"
&Ticker
&"/"
&"history?p="
&Ticker
)
),
Data2 = Source{2}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data2,{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close*", type number}, {"Adj Close**", type number}, {"Volume", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)
in
#"Added Index"
And in this function it already does the Index from above. But that was just more for me and just seeing what can be in PQ, so probably not needed for your case as you probably have a data source already.
Let me know what you think.
Moving Aveage, Based on data not # of actual days.pbix (265.4 KB)