Forum Discussion

hnsbhat's avatar
hnsbhat
Icon for Helper I rankHelper I
8 years ago
Solved

How to get previous Nth record Value from the specific date

I have a table which has Date, Stock symbol, Open, high, low , close. I have data for only for week days and in some cases week days also doesn't have data because of market holiday. What I am trying ...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi hnsbhat

 

Try this Measure for Close 1

 

Close 1=
VAR Noofrecords =
    IF (
        HASONEVALUE ( Table1[Dates] ),
        COUNTROWS (
            FILTER (
                ALLEXCEPT ( Table1, Table1[Symbol] ),
                Table1[Dates] < VALUES ( Table1[Dates] )
            )
        )
    )
RETURN
    CALCULATE (
        MAX ( Table1[Close] ),
        EXCEPT (
            TOPN (
                Noofrecords - 1,
                ALLEXCEPT ( Table1, Table1[Symbol] ),
                Table1[Dates], ASC
            ),
            TOPN (
                Noofrecords - 2,
                ALLEXCEPT ( Table1, Table1[Symbol] ),
                Table1[Dates], ASC
            )
        )
    )