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

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Hi hnsbhat

       

      Good One

       

      Check this file as well.

       

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

       

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago