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
                )
                )
ElliotP's avatar
ElliotP
Post Prodigy
8 years ago

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

    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's avatar
      ElliotP
      Post Prodigy
      8 years ago

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

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

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