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

 

Please try this MEASURE

 

Please see the attached file here

Previous_25th_Price =
VAR Noofrecords =
    COUNTROWS (
        FILTER ( ALL ( Table1 ), Table1[Dates] < SELECTEDVALUE ( Table1[Dates] ) )
    )
RETURN
    CALCULATE (
        VALUES ( Table1[Price] ),
        EXCEPT (
            TOPN ( Noofrecords - 24, ALL ( Table1[Dates] ), Table1[Dates], ASC ),
            TOPN ( Noofrecords - 25, ALL ( Table1[Dates] ), Table1[Dates], ASC )
        )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi hnsbhat

 

Similarly you can get the previous 25th date using the MEASURE below

 

Previous_25th_Date =
VAR Noofrecords =
    COUNTROWS (
        FILTER ( ALL ( Table1 ), Table1[Dates] < SELECTEDVALUE ( Table1[Dates] ) )
    )
RETURN
    CALCULATE (
        MAX ( Table1[Dates] ),
        EXCEPT (
            TOPN ( Noofrecords - 24, ALL ( Table1[Dates] ), Table1[Dates], ASC ),
            TOPN ( Noofrecords - 25, ALL ( Table1[Dates] ), Table1[Dates], ASC )
        )
    )
  • hnsbhat's avatar
    hnsbhat
    Icon for Helper I rankHelper I
    8 years ago

    Thanks for your reply, I tried it, but since I was trying it in excel 2016 powerpivot, it seems it doesn't have function SELECTEDVALUE. I searched for alternative and found this - IF ( HASONEVALUE ( Table[column] ), VALUES ( Table[column] ) ). But even with this I am not getting any values but only Blank. 

    • 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
            )
        )
    )