DAX Calculated Column to show the First Value of another column

[Anonymous]

Guys, I need your help to create a calculated column (##First Real Lead Time) to return (##Real Lead Time) based on first date and the type of (##WO+LN), like this:

Date	##WO+LN	##Real Lead Time	##First Real Lead Time
9/5/2022	1234.1	22	211
8/29/2022	1234.1	22	211
8/22/2022	1234.1	22	211
8/15/2022	1234.1	29	211
8/8/2022	1234.1	211	211
9/5/2022	1234.2	22	119
8/29/2022	1234.2	22	119
8/22/2022	1234.2	22	119
8/15/2022	1234.2	29	119
8/8/2022	1234.2	119	119

Many tks,

Alexandre

[Ashish_Mathur]

Hi,

This calculated column formula works

=LOOKUPVALUE([##Real Lead Time],data[Date],CALCULATE(MIN(data[Date]),FILTER(data,data[##WO+LN]=EARLIER([##WO+LN]))),data[##WO+LN],data[##WO+LN])

Hope this helps.

[Anonymous]

Hello Ashish,

I was already looking for lookupvalue and ealier, but I couldn't find the same like you suggested.

Anyway... I use your example, and I got this error message:

Remember, I'm using this as a calculated column.

I was wondering, may you explain, why you use twice on the end the "data[##WO+LN]"? I want to understand your logic to learn a little more.

So, tks for your time.

---

Alexandre

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file and show the expected result there.

[Anonymous]

Here you go:

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

tks,

Alexandre

[Ashish_Mathur]

I am unable to open the file.  When i double click, i just see the splash screen.

[Anonymous]

Download it. Click on red highlight.

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

[Ashish_Mathur]

The problem is not with downloading.  When i double click to open the file, the file freezes at the splash screen.  I am able to open other PowerBI Desktop files just fine.

[Anonymous · Accepted solution]

oh no!!!
Anyway, I have find a solution.

I follow this aproach: https://stackoverflow.com/questions/73722277/find-the-first-value-by-category-dax

First, I created a Index column on Power Query.

After that, I created a calculated column with Group Rank:

Group Ranking =

RANKX (

    FILTER (

        Sheet2,

        EARLIER ( Sheet2[##WO+LN] ) = Sheet2[##WO+LN]

    ),

    Sheet2[Index],

    ,

    ASC,

    DENSE

)

and finally, I added another calculated column:

##First Real Lead Time =

VAR _firstleadtime = CALCULATE(Min(Sheet2[Group Ranking]),ALLEXCEPT(Sheet2,Sheet2[##WO+LN]))

return

    CALCULATE ( FIRSTNONBLANK( Sheet2[##Real Lead Time],1) , ALLEXCEPT(Sheet2,Sheet2[##WO+LN]),Sheet2[Group Ranking] = _firstleadtime)

Now, It's working.

Many tks, Ashish for your time.

---

Alexandre.