Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
8 years ago

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 tab...
Need Help
Show and Tell
Tutorial Requests
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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
                )
                )
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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