Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ElliotP
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

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
Phil_Seamark
Employee
Employee

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!

Thanks for the quick reponse. 

 

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

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!

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!

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

 


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

Proud to be a Datanaut!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.