Forum Discussion

NeilAm12's avatar
NeilAm12
Icon for Helper I rankHelper I
7 years ago
Solved

Additional Column for Previous Data Based on Previous Time and Filter

Hello All,   Need your help, i need to get the previous data of Data-Xi column base on Entry Date/Time based with filter of MiniCompany and Machine #. I tried many formula but it shows error. Con...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    I think this error is occuring because there is a duplication combination of Mini Company, Entry Date/Time and Machine#.  For the LOOKUPVALUE() function to work, each row combination should be unique

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

This calculated column formula works

=LOOKUPVALUE(Data[Data-Xi],Data[Mini Company],Data[Mini Company],Data[Machine#],Data[Machine#],Data[Entry Date/Time],CALCULATE(MAX(Data[Entry Date/Time]),FILTER(Data,Data[Mini Company]=EARLIER(Data[Mini Company])&&Data[Machine#]=EARLIER(Data[Machine#])&&Data[Entry Date/Time]<EARLIER(Data[Entry Date/Time]))))

Hope this helps.

  • NeilAm12's avatar
    NeilAm12
    Icon for Helper I rankHelper I
    7 years ago

    Hello Ashish,

     

    I tried the formula but it shows error, btw my entry/date time is not in chronoloical order i tried to sort it but i think PBI follows only in the 1 number.

     

    RRA2 = LOOKUPVALUE(DBB[Data-Xi],DBB[Mini Company],DBB[Mini Company],DBB[Machine#],DBB[Machine#],DBB[Entry Date/Time],CALCULATE(MAX(DBB[Entry Date/Time]),FILTER(DBB,DBB[Mini Company]=EARLIER(DBB[Mini Company])&&DBB[Machine#]=EARLIER(DBB[Machine#])&&DBB[Entry Date/Time]<EARLIER(DBB[Entry Date/Time]))))

     

    Thanks for the help.

     

    Regards,

    Neil

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

      The formula definitely works (as evident from my screenshot).  Share the link from where i can download your PBI file.  Ensure that there are no errors in any cell of the columns which are being referred to in my formula.

      • NeilAm12's avatar
        NeilAm12
        Icon for Helper I rankHelper I
        7 years ago

        Hello,

         

        I cant attach my PBI file here. Can you share your mail to me so i will mail you my PBI file.

         

        Regards,

        Neil