Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Previous Day Value

I have a date dimension table that has indicator column for working and non working days. The date dimension table has an active relationship with a report fact table. The report fact table consists ...
Tips
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    Hi Anonymous ,

     

    Yes, if you already have a DAX measure that calculates the stock price, you can incorporate it into other DAX calculations. For example, if you have a measure named Stock Price defined as SUM('StockData'[Price]), you can reference it in other formulas seamlessly.

    To calculate the cumulative stock price over time, you could use a formula like:

    Cumulative Stock Price = 
CALCULATE(
    [Stock Price],
    FILTER(
        ALL('DateTable'),
        'DateTable'[Date] <= MAX('DateTable'[Date])
    )
)

    This measure calculates the total stock price up to the current date within the visual's context. Similarly, if you want to rank stocks based on their price, you can create a measure such as:

    Rank by Stock Price = 
RANKX(
    ALL('StockData'[StockName]),
    [Stock Price],
    ,
    DESC,
    Dense
)

    Here, the Stock Price measure is used to determine each stock's rank in descending order of price. For more advanced calculations, like finding the percentile rank of the stock price, you could write:

    Percentile Rank Stock Price = 
DIVIDE(
    RANKX(
        ALL('StockData'[StockName]),
        [Stock Price],
        ,
        ASC,
        Dense
    ) - 1,
    COUNTROWS(ALL('StockData'[StockName])),
    0
)

    In this case, the Stock Price measure integrates into a formula to calculate how a stock's price compares to others. This approach allows your existing measure to be dynamically used within more complex calculations. Let me know if you need assistance with tailoring a specific scenario!

     

    Best regards,

DataNinja777's avatar
DataNinja777
Super User
1 year ago

Hi Anonymous ,

 

To solve this, you can create a DAX measure that retrieves the value from the most recent working day for non-working days. Since your date dimension table includes an indicator column for working and non-working days and your fact table contains data only for working days, the measure will first identify the last working day before the current date and then retrieve the corresponding value.

The DAX measure can be written as follows:

Previous Working Day Value = 
VAR CurrentDate = MAX('Date'[Date])
VAR PreviousWorkingDay = 
    CALCULATE(
        MAX('Date'[Date]),
        'Date'[Date] < CurrentDate,
        'Date'[IsWorkingDay] = TRUE()
    )
RETURN
    CALCULATE(
        MAX('FactTable'[Stock Price]),
        'Date'[Date] = PreviousWorkingDay
    )

In this measure, CurrentDate captures the current date based on the report context. The variable PreviousWorkingDay calculates the most recent working day by filtering the date dimension table to find the maximum date that is earlier than CurrentDate and marked as a working day (IsWorkingDay = TRUE). Finally, the RETURN statement fetches the stock price from the fact table for the identified PreviousWorkingDay. If there are multiple rows in the fact table for each date, you can adjust the aggregation function, such as using SUM or AVERAGE, as needed. This approach ensures that the stock price remains static during non-working days by carrying forward the value from the last working day.

 

Best regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi DataNinja777 , lets say if I have a DAX measue that calculate the stock price, is there a way to apply into the meausre you provided?

    • DataNinja777's avatar
      DataNinja777
      Super User
      1 year ago

      Hi Anonymous ,

       

      Yes, if you already have a DAX measure that calculates the stock price, you can incorporate it into other DAX calculations. For example, if you have a measure named Stock Price defined as SUM('StockData'[Price]), you can reference it in other formulas seamlessly.

      To calculate the cumulative stock price over time, you could use a formula like:

      Cumulative Stock Price = 
CALCULATE(
    [Stock Price],
    FILTER(
        ALL('DateTable'),
        'DateTable'[Date] <= MAX('DateTable'[Date])
    )
)

      This measure calculates the total stock price up to the current date within the visual's context. Similarly, if you want to rank stocks based on their price, you can create a measure such as:

      Rank by Stock Price = 
RANKX(
    ALL('StockData'[StockName]),
    [Stock Price],
    ,
    DESC,
    Dense
)

      Here, the Stock Price measure is used to determine each stock's rank in descending order of price. For more advanced calculations, like finding the percentile rank of the stock price, you could write:

      Percentile Rank Stock Price = 
DIVIDE(
    RANKX(
        ALL('StockData'[StockName]),
        [Stock Price],
        ,
        ASC,
        Dense
    ) - 1,
    COUNTROWS(ALL('StockData'[StockName])),
    0
)

      In this case, the Stock Price measure integrates into a formula to calculate how a stock's price compares to others. This approach allows your existing measure to be dynamically used within more complex calculations. Let me know if you need assistance with tailoring a specific scenario!

       

      Best regards,