Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Afternoon,
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.
Thoughts?
Solved! Go to Solution.
Here is a slightly different calculated measure
Measure = VAR CompanyName = MAX('ReferenceTable'[CompanyName]) VAR LastPRiceTime = CALCULATE( MAX('StockBarDataExample'[date]), FILTER( ALL('StockBarDataExample'), 'StockBarDataExample'[Date.1] = MAX('StockBarDataExample'[Date.1]) ) ) RETURN CALCULATE( MAX('StockBarDataExample'[close]) , FILTER(ALL('StockBarDataExample'),'StockBarDataExample'[date] = LastPRiceTime), FILTER(ALL('ReferenceTable'[CompanyName]),'ReferenceTable'[CompanyName] = CompanyName) )
Hi @ElliotP
This calculated measure might be close
Last Price per Day = VAR LastPriceTime = CALCULATE( LASTNONBLANK('StockBarDataExample'[Time],1), FILTER('ReferenceTable','ReferenceTable'[CompanyName] = MAX('ReferenceTable'[CompanyName])), FILTER('DateTable','DateTable'[DateKey] = MAX('DateTable'[DateKey])) ) RETURN CALCULATE(MAX(StockBarDataExample[close]),'StockBarDataExample'[Time] = LastPriceTime)
Here is a slightly different calculated measure
Measure = VAR CompanyName = MAX('ReferenceTable'[CompanyName]) VAR LastPRiceTime = CALCULATE( MAX('StockBarDataExample'[date]), FILTER( ALL('StockBarDataExample'), 'StockBarDataExample'[Date.1] = MAX('StockBarDataExample'[Date.1]) ) ) RETURN CALCULATE( MAX('StockBarDataExample'[close]) , FILTER(ALL('StockBarDataExample'),'StockBarDataExample'[date] = LastPRiceTime), FILTER(ALL('ReferenceTable'[CompanyName]),'ReferenceTable'[CompanyName] = CompanyName) )
@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?
@Phil_Seamark Thanks for the quick response.
Edit; my initial response was incorrect.
Pic: https://gyazo.com/3d721156f7a575df0c12990cc02b2eed
It seems to find the value at 12am and not at 6am
HI @ElliotP
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.