Data from Measure for Last Trading Day

[ElliotP]

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"

[Phil_Seamark]

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

[ElliotP]

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

[Phil_Seamark]

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.

[ElliotP]

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

[Phil_Seamark]

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

[ElliotP]

hmmm

[Phil_Seamark]

Please try this PBIX file to see if it is faster

[ElliotP]

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?

[ElliotP]

now it's coming out with values like 28,408 after I went through and fixed up a wrong column; hmmm

[Phil_Seamark]

You already have a Date table and we don't need a time table.

How is your working PBIX different to the one I sent in terms of data?

[ElliotP]

I've had a look; I'm honestly not sure. Aside from there being a lot more data and the table page being filtered by a specific company; should we incorporate a filter on a specific company again?

I don't see any noticeable differences.

[ElliotP]

I've had a look again. I really can't see any difference. I've noticed you've added some test measures in both the datetable and the fact table; but I can't see their influence upon our final created measures.

hmmm

[Phil_Seamark]

Oh they may be ones I was just messing around with.  I created a calculated column in your Date table to help filter (index) faster,

And two calculated columns that show the close price for Today and Last Trading Date end with II

Probably need to add back in the filter for company name.  I'd suggest merging this table to the main table for performance reasons.

[ElliotP]

How would I add the filter for the company name back to the closingpricedaybeforeII measure?

I'm still curious about a time intelligence function or if its worth creating tables and then just working off that? Since the tables would be worked on when the model is created it might solve the performance issue and let us use time intelligence functions.

[ElliotP]

Feelings anyone?

I'm curious if we are able to create the ClosingPriceDayBefore measure using time intelligence functions?

I'm not sure why at the moment I'm unable to use the ClosingPrice measure as the expression in a calculate function, hmmm

[Phil_Seamark]

HI @ElliotP

The main reason we can't simply subtract a day to the ClosingPrice measure is that the point in time for the previous trading day might be at a different part of the day.  We still have to write DAX to establish what the maximum point in time is for the previous trading day and we have that.

[ElliotP]

@Phil_SeamarkThanks for the response.

If that would make life easier, we can to be honest. I would be happy to eat that inconsistency as it honestly never happens in this dataset. The dataset always ends at 4pm every single trading day except perhaps once per year.

I have scoured both pbix's and I honestly can't find the difference between the two. Both have the same relationships, data types; so I'm completly lost as to what the issue is.

Closing Price II = 
    CALCULATE(
        MAX('StockBarData'[close]),
        LASTNONBLANK(StockBarData[LocalDateTime],1),
        FILTER(ALL('EquityReferenceTable'[CompanyName]),'EquityReferenceTable'[CompanyName]=MAX('EquityReferenceTable'[CompanyName])),
        'DateTable'[TradingDay]="1"
        )

I feel I understand out closing price measure. We filter the fact table to find the LASTNONBLANK datetime. We then filter the related Reference table as to find only the company we are looking. We finally have a condition where the Datetable must be a trading day.

Closing Price Day Before II = 
VAR x = MAX('DateTable'[Trading Day Index])
VAR y = MAXX(FILTER(ALL('StockBarData'),RELATED('DateTable'[Trading Day Index]) = x - 1),'StockBarData'[LocalDateTime])
RETURN 
MAXX(FILTER(ALL('StockBarData'[close],'StockBarData'[LocalDateTime]),'StockBarData'[LocalDateTime] = y),'StockBarData'[close])

In our ClosingPriceDayBeforeII measure we are creating variable x which returns the maximum trading day index. We then create a variable which is maximum datetime resultant from creating a table that removes the filter context upon the fact table and then -1's from the from Trading Day Index whole number column.  We then find the close after creating a table with all of the filter context's removed from the columns Close & the date time column where our datetime column equals our x variable.

I'm wondering how we could incorporate the CompanyNameReferencetable, would we just add the column to the ALL clause and set it to a newly created variable finding the MAX company name?

I have tried playing around with the formula's but I'm just not sure. When I do the below it does not seem to respect the filter for TradingDay:

ClosingPriceDayBefore = 
CALCULATE([Closing Price II],
DATEADD(DateTable[DateKey], -1, DAY),
DateTable[TradingDay] = "1"
)
        

[ElliotP]

I've added a new pbix file which pulls directly from the sql table; it is the same file as my working file.

Link: https://1drv.ms/u/s!At8Q-ZbRnAj8iGW8_wTuj5HnlsJj

It seems to be struggling with the dates; not sure.

[ElliotP]

Feelings?

[Phil_Seamark]

Will have a look in the morning