I've got some stock bar data with hourly close values for each security for the past few years. I'm trying to find the last value in the close column for each day.
I have my date and time reference tables setup with relationships.
I have tried some Calculate expressions with ALL & =Max(DateTable[Datekey]) but I haven't been able to find the right solution.
Go to Solution.
Here is a slightly different calculated measure
VAR CompanyName = MAX('ReferenceTable'[CompanyName])
VAR LastPRiceTime =
'StockBarDataExample'[Date.1] = MAX('StockBarDataExample'[Date.1])
FILTER(ALL('StockBarDataExample'),'StockBarDataExample'[date] = LastPRiceTime),
FILTER(ALL('ReferenceTable'[CompanyName]),'ReferenceTable'[CompanyName] = CompanyName)
Proud to be a Datanaut!
View solution in original post
Do you have a small example of what your data might look like?
Thanks for the quick reponse.
Here is a link: https://1drv.ms/f/s!At8Q-ZbRnAj8iFqr4YYl2zYRrm9E
This calculated measure might be close
Last Price per Day =
VAR LastPriceTime =
FILTER('ReferenceTable','ReferenceTable'[CompanyName] = MAX('ReferenceTable'[CompanyName])),
FILTER('DateTable','DateTable'[DateKey] = MAX('DateTable'[DateKey]))
CALCULATE(MAX(StockBarDataExample[close]),'StockBarDataExample'[Time] = LastPriceTime)
@Phil_Seamark Thanks for the quick response.
Edit; my initial response was incorrect.
It seems to find the value at 12am and not at 6am
The date field you are using in the bottom visual is different t the date field in the top one. Do you have a preference which you would rather use?
@Phil_Seamark Ideally I would like to be able to use the date from the datetable.
The two tables are to illustrate the difference betwen the fact table where the data per row is by the date+time while the date table is of course just dates.
@Phil_SeamarkThank you so much, I really appreciate it.
I tried using the date table as a the data reference and it didn't seem to work, I'm not sure why.
I suspect I need to either make the table relationships bidirectional or use Related() for the filter context?
Check out the on demand sessions that are available now!
Check out the Winners!
Features releasing from October 2020 through March 2021