Additional Column for Previous Data Based on Previous Time and Filter

[NeilAm12]

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 :

RRA = CALCULATE(MAX(DB[Data-Xi]),ALL(DBB),DBB[Concatenate] = EARLIER(DBB[Concatenate]), DB[Entry Date/Time] = EARLIER(DBB[Entry Date/Time]))

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

[Ashish_Mathur]

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]

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]

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]

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]

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]

Hello,

Please see below link.

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

Regards,

Neil

[Ashish_Mathur]

Hi,

That requires signing in.  Try to share the file via Google Drive.

[NeilAm12]

Hello,

Please try this.

https://drive.google.com/drive/folders/1lfkxvhA9Qll5lylU3GGsdl3UoluqxNzT?usp=sharing

Regards,

Neil

[Ashish_Mathur · Accepted solution]

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