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

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

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      Hi NeilAm12 ,

       

      You could upload your sample PBIX file onto OneDrive and share the downloaded link here. Remember to hide sensitive data in file.

       

      Best regards,

      Yuliana Gu

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

        Hello,

         

        Please see below link.

        I don't know if you can access it, this is my 1st time to use OneDrive.

         

        Regards,

        Neil