Forum Discussion
Helper IAdditional 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.
Concatenate: manual created column.
RRA Formula :
Below is my data without filter of concatenate:
Filter od concatenate:
My RRA data shows the same value in Data-Xi column not the previous data based on concatenate filter.
I rank manualy in excel the entry date/time. RRA 1st data shoul be show blank and the rest is based on previous data if you filter concatenate(Mini Company and Machine).
I need the correct formula of RRA.
Expected data:
| Lot ID | Mini Company | Entry Date/Time | Machine# | Data-Xi | Concatenate | RRA |
| 63500 | DIEBOND_BONDFORCE_BS | 5/1/2019 7:20 | MRSI 11 | 39.84 | DIEBOND_BONDFORCE_BSMRSI 11 | |
| 63600 | DIEBOND_BONDFORCE_BS | 5/1/2019 15:03 | MRSI 11 | 37.98 | DIEBOND_BONDFORCE_BSMRSI 11 | 39.84 |
| 63700 | DIEBOND_BONDFORCE_BS | 5/1/2019 23:04 | MRSI 11 | 40.05 | DIEBOND_BONDFORCE_BSMRSI 11 | 37.98 |
| 24700 | DIEBOND_BONDFORCE_BS | 5/2/2019 7:00 | MRSI 11 | 39.1 | DIEBOND_BONDFORCE_BSMRSI 11 | 40.05 |
| 24700 | DIEBOND_BONDFORCE_BS | 5/2/2019 19:02 | MRSI 11 | 38.6 | DIEBOND_BONDFORCE_BSMRSI 11 | 39.1 |
| 24800 | DIEBOND_BONDFORCE_BS | 5/3/2019 0:19 | MRSI 11 | 39.77 | DIEBOND_BONDFORCE_BSMRSI 11 | 38.6 |
| 24800 | DIEBOND_BONDFORCE_BS | 5/3/2019 15:01 | MRSI 11 | 38.64 | DIEBOND_BONDFORCE_BSMRSI 11 | 39.77 |
| 24800 | DIEBOND_BONDFORCE_BS | 5/3/2019 7:00 | MRSI 11 | 41.123 | DIEBOND_BONDFORCE_BSMRSI 11 | 38.64 |
| 343800 | DIEBOND_BONDFORCE_BS | 5/4/2019 3:09 | MRSI 11 | 39.44 | DIEBOND_BONDFORCE_BSMRSI 11 | 41.123 |
| 343900 | DIEBOND_BONDFORCE_BS | 5/4/2019 7:20 | MRSI 11 | 39.69 | DIEBOND_BONDFORCE_BSMRSI 11 | 39.44 |
| 344000 | DIEBOND_BONDFORCE_BS | 5/4/2019 15:46 | MRSI 11 | 37.36 | DIEBOND_BONDFORCE_BSMRSI 11 | 39.69 |
| 344000 | DIEBOND_BONDFORCE_BS | 5/4/2019 22:46 | MRSI 11 | 38.92 | DIEBOND_BONDFORCE_BSMRSI 11 | 37.36 |
| 394900 | DIEBOND_BONDFORCE_BS | 5/5/2019 7:04 | MRSI 11 | 39.23 | DIEBOND_BONDFORCE_BSMRSI 11 | 38.92 |
| 395000 | DIEBOND_BONDFORCE_BS | 5/5/2019 15:18 | MRSI 11 | 40.97 | DIEBOND_BONDFORCE_BSMRSI 11 | 39.23 |
| 395100 | DIEBOND_BONDFORCE_BS | 5/5/2019 22:58 | MRSI 11 | 38.48 | DIEBOND_BONDFORCE_BSMRSI 11 | 40.97 |
Thanks,
Neil
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
9 Replies
- Ashish_Mathur
Super User
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.
NeilAm12Hello 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
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.