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

 

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
            )
        )
    )
    • hnsbhat's avatar
      hnsbhat
      Icon for Helper I rankHelper I
      8 years ago

      Hi Zubair, Thank you, It works. However it only works when I try with the sample data I had provided, if I try with complete data nothing is coming again. I am giving the links for both sample and complete data file I have used below.

      And with test data also I am not able to change the number of records to look back.

       

       

      Test Data - https://drive.google.com/open?id=18F7wQ4IkDwv2IQ4Cc3-0vgNeMaDFYaaD

      Complete data - https://drive.google.com/open?id=1VSTxwxI4Hphcyp7MOn4K4KCG2GRsHqc_

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

        Thank you All for your help, I am able to get what I wanted by using the below approach.

        1. Add a Index Column (Using Query Editor)

        2. Create a calculated Column in powerpivot

         

        Rank =
VAR Symbol = Table1[Symbol]
RETURN
    RANKX (
        FILTER ( ALL ( Table1 ), Table1[Symbol] = Symbol ),
        Table1[Index],
        ,
        ASC
    )

        3. Create The PreviousRow Column Or in a Measure (Replace VAR Index = Table1[RANK]-1 with VAR Index=min(Table1[Rank])-1)  

         

        Close -1 =
VAR Index = Table1[Rank] - 1
RETURN
    CALCULATE (
        SUM ( Table1[Close] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[Close] ), Table1[Rank] = Index )
    )