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

Data from Measure for Last Trading Day

Afternoon,

 

I've attached a pbix for anyone that wants to have a look.

 

I've created a measure called [ClosingPrice] which calculates the last Stock price from my dataset for each day (Fact table is titled "StockBarDataExample").

 

I would then also like to create a measure which shows the final price from the day before.

 

I have created a measure titled "ClosingPriceDayBefore" and have tried to use a time intelligence function to go back one day. My main issue is that I need to filter as so only days which are TradingDays (column in my DateTable) are considered in the filter context. I have tried applying the filter upon both measures but I feel it would be best on the first measure (ClosingPrice) and as so it's residually applied to the second measure "ClosePriceDayBefore" which is just the first measure wrapped in a CALCULATE function.

 

So for example in the pbix attached; Monday the 16th of April in the ClosingPriceDayBefore Column should show 59.46 and not the same value as ClosingPrice for that Monday.

 

Any ideas why the filter isn't applying even when I have tried to apply the filter on both measures (I'm not sure how to integrate the TradingDay = '1' as a variable, i'm not sure if that would help).

 

OneDrive Link: https://1drv.ms/f/s!At8Q-ZbRnAj8iFqr4YYl2zYRrm9E

The pbix under consideration is titled "PowerBIForumExample"

21 REPLIES 21
Highlighted
Microsoft
Microsoft

Hi @ElliotP

 

This is close, but I think your [closing price] measure is wrong.  I cant see a 59.46 value in the data. You are doubling with the SUM

 

ClosingPriceDayBefore = 
Var LastTme = 
    Maxx(
        filter(
            all('StockBarDataExample'),
            RELATED(DateTable[TradingDay])="1" && 
            'StockBarDataExample'[Date.1] < MAX('StockBarDataExample'[Date.1]) &&
            RELATED('ReferenceTable'[CompanyName]) = MAX('ReferenceTable'[CompanyName])
            ) ,
            'StockBarDataExample'[date]
            )
return 
    CALCULATE(
            max('StockBarDataExample'[close]),
            filter(
                all('StockBarDataExample'),
                RELATED(ReferenceTable[CompanyName])=max('ReferenceTable'[CompanyName])
                && 'StockBarDataExample'[date] = LastTme
                )
                )            

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

Proud to be a Datanaut!

Highlighted

Try this for your [closing price] measure

 

Closing Price II = 
    CALCULATE(
        MAX('StockBarDataExample'[close]),
        LASTNONBLANK(StockBarDataExample[date],1),
        FILTER(ALL('ReferenceTable'[CompanyName]),'ReferenceTable'[CompanyName]=MAX('ReferenceTable'[CompanyName])),
        'DateTable'[TradingDay]="1"
        )

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

Proud to be a Datanaut!

Highlighted

@Phil_Seamark

 

When I try to pop in the ClosingPriceDayBeforeII (updated measure) my table visual just hangs and consumes all of my CPU. Is there a way to write the code so its a little more "efficient"?

 

Would you mind also explaining the DAX logic behind ClosingDayBeforeIII please. I'm not sure why we need to include the [CompanyName] filter. I've been reading Ferrari's definitive guide to dax so I'm trying to learn the inner workings.

Highlighted

HI @ElliotP

 

We can definitely make this faster.  

 

The reason I include the company name is that I assume there might be other companies and you want to track the closing price per company.

 

 


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

Proud to be a Datanaut!

Highlighted

@Phil_SeamarkThere are. So the filter context wouldn't determine that naturally?

Highlighted

Not if we are clearing all the filters from the context using the ALL function


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

Proud to be a Datanaut!

Highlighted

hmmm

Highlighted

Please try this PBIX file to see if it is faster

 

 


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

Proud to be a Datanaut!

Highlighted

When I try to apply the solution to my working pbix it shows values like "170.51" for the ClosingPriceDayBeforeII.

 

I have added in the Trading Day Index to the datetable.


Why aren't we able to use the time intelligence functions or perhaps create a datetime table?

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

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors