cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Final Value of Day

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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)
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Highlighted
Microsoft
Microsoft

Hi @ElliotP

 

Sounds do-able.

 

Do you have a small example of what your data might look like?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted

Thanks for the quick reponse. 

 

Here is a link: https://1drv.ms/f/s!At8Q-ZbRnAj8iFqr4YYl2zYRrm9E

Highlighted

p2.pngHi @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)

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted

@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

Highlighted

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?

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted

@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.

Highlighted

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)
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted

@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?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors