Solved: Re: How to calculate daily stock return using DAX ...

Anonymous · ‎12-11-2020

Hi all,

I want to calculate the daily return for this stock price (ignoring the weekend) by creating a new measure. May I know how should I do that by using DAX formula? I'm quite new to PowerBI. Thank you.

mahoneypat · ‎12-12-2020

Here is one way to do it. Use this measure expression in a table visual with the date column from your stock table. Replace Stock with your actual table name.

Change From Prev Day =
VAR thisvalue =
    MAX ( Stock[Close] )
VAR thisdate =
    MAX ( Stock[Date] )
VAR prevvalue =
    CALCULATE (
        LASTNONBLANKVALUE (
            Stock[Date],
            MAX ( Stock[Close] )
        ),
        FILTER (
            ALL ( Stock[Date] ),
            Stock[Date] < thisdate
        )
    )
RETURN
    DIVIDE (
        thisvalue - prevvalue,
        prevvalue
    )

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

View solution in original post

mahoneypat · ‎12-12-2020

Here is one way to do it. Use this measure expression in a table visual with the date column from your stock table. Replace Stock with your actual table name.

Change From Prev Day =
VAR thisvalue =
    MAX ( Stock[Close] )
VAR thisdate =
    MAX ( Stock[Date] )
VAR prevvalue =
    CALCULATE (
        LASTNONBLANKVALUE (
            Stock[Date],
            MAX ( Stock[Close] )
        ),
        FILTER (
            ALL ( Stock[Date] ),
            Stock[Date] < thisdate
        )
    )
RETURN
    DIVIDE (
        thisvalue - prevvalue,
        prevvalue
    )