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

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

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!

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.