how to get Min value of text column from Earlier function

[ashc_cool10]

Hi Team,

I am stuck in one calculation in which I need to check the Minimum Value of "CHANGENR" column and populate the "VALUE_OLD" entry in front of every column .

Please see below for more calrification :

I trired using Min and Earlier function to compare these column but its giving me wrong value. Request you to please let me know what needs to be corrected in this:

check_1233 = CALCULATE(MIN(CV_CLIP[CDPOS.VALUE_OLD]),FILTER(CV_CLIP,CV_CLIP[CDPOS.CHANGENR]=EARLIER(CV_CLIP[CDPOS.CHANGENR])))

@Ashish_Mathur  

Thanks,

Ashish

[ashc_cool10]

..

ashish

[Ashish_Mathur]

I am taken to a sign-in page.

[ashc_cool10]

Please check below link and try to downlaod from this

https://drive.google.com/file/d/1bdLVYS3h0FCXV_QeOAFs3Oo_iIP2n2uY/view?usp=sharing 

Thanks,

Ashish

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Reslt = LOOKUPVALUE(Sheet1[VALUE .OLD],Sheet1[CHANGENR],CALCULATE(MIN(Sheet1[CHANGENR]),FILTER(Sheet1,Sheet1[OBJECT.ID]=EARLIER(Sheet1[OBJECT.ID]))),Sheet1[OBJECT.ID],Sheet1[OBJECT.ID])

Hope this helps.

[ashc_cool10]

Hi @Ashish_Mathur ,

Thank you for your help !

It works finally . You are a star.

Regards,

Ashish

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.

[Ashish_Mathur]

Hi,

Cannot understand your question.  The entries in the CHANGENR column are text.  How can you take the minimum of a column of text entries?

[ashc_cool10]

Hi @Ashish_Mathur ,

You are right data type of column"CHANGENR" is Text whoever i can change it to "Whole Number" .

But i am not sure why i am getting wrong result .

Is there any solution how can i achieve this?

Thanks,

Ashish

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = lookupvalue(Data[VALUE OLD],Data[CHANGENR],min(Data[CHANGENR]))

Hope this helps.

[ashc_cool10]

Hi @Ashish_Mathur ,

I have changed the format of "Changenr","OBJECTID"  and "Value OLD" from Text to whole  Number  and then I tried the above forumla however i am getting the same value on each entry . 

As we know Lookup function will work as VLOOKUP in which whenever there is  a first exact match of Min value in CHANGENR column it will populate the first entry each row however It should give me  if its "OBJECT ID" is different then it should  check "Min" value of that "CHANGENR" Column and should pick the "Value Old entry " .

To make it more clear please see below screenshot of desire result :

I hope i am make it clear make requirement now.

Request you to please help .

Regards,

Ashish

[Ashish_Mathur]

 Share the download link of the PBI file.