Previous Day Value

[keith515]

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 of daily report only for working days. How do I create a DAX that returns me the value from previous working days for non working day? (e.g. The stock price for portfolio from previous working day will remain the static during non working day)

Any ideas and solutions are greatly appreciated!!

[gmsamborn]

Hi @keith515 

My solution is a little different.  I added another calculated column in the Date table that can be very useful.  (I'm not sure how your working day indicator so it might need a small change.)

Working Day =
IF(
    NOT WEEKDAY( 'Date'[Date], 2 ) IN { 6, 7 },
    TRUE(),
    FALSE()
)


Working Day Number =
RANKX(
    FILTER(
        'Date',
        'Date'[Working Day]
    ),
    'Date'[Date],
    ,
    ASC
) - NOT 'Date'[Working Day]

Then the measure is pretty straight-forward.

PrevWorkingDayValue = 
VAR _PrevDayNo = MAX( 'Date'[Working Day Number] ) - 1
RETURN
	CALCULATE(
		SUM( 'Table'[Amt] ),
        ALL( 'Date' ),
		'Date'[Working Day Number] = _PrevDayNo
	)

Let me know if you have any questions.

[DataNinja777]

Hi @keith515 ,

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,

[keith515]

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 · Accepted solution]

Hi @keith515 ,

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,