Formula not showing earliest Value

[ratercero]

Hello, 

Im looking to determine the first dyelot nomber produces with a certain color and Yarn lot, the formula keeps showing another dyelot number:

Here is the formula I used:

Yarn First Dye = 
CALCULATE (
    FIRSTNONBLANK ( HILAZA_ACUM[DYELOT]; HILAZA_ACUM[DYELOT] );
    CALCULATETABLE (
        HILAZA_ACUM;
        ALLEXCEPT ( HILAZA_ACUM; HILAZA_ACUM[LOTE HILAZA DEPURADO];HILAZA_ACUM[Cod. Color] );
        MIN(HILAZA_ACUM[FECHA];HILAZA_ACUM[FECHA])
    )
)

This is what I get:

I dont understand why but Ignores values that start with alfabetical caracters and it should not.

Please advise how to resolve.

Best Regards

RT

[v-jiascu-msft]

Hi @ratercero,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @ratercero,

Try this formula, please.

Measure =
VAR earliestDate =
    CALCULATE (
        MIN ( HILAZA_ACUM[FECHA] ),
        ALLEXCEPT (
            HILAZA_ACUM,
            HILAZA_ACUM[Cod. Color],
            HILAZA_ACUM[LOTE HILAZA DEPURADO]
        )
    )
RETURN
    CALCULATE (
        MIN ( HILAZA_ACUM[DYELOT] ),
        FILTER (
            ALLEXCEPT (
                HILAZA_ACUM,
                HILAZA_ACUM[Cod. Color],
                HILAZA_ACUM[LOTE HILAZA DEPURADO]
            ),
            HILAZA_ACUM[FECHA] = earliestDate
        )
    )

Best Regards,

Dale

[Anonymous]

If you mean first Dyelot with regards to FECHA, maybe you could use this:

CALCULATE( FIRSTNONBLANK (HILAZA_ACUM [DYELOT] ) , 
FILTER ( HILAZA_ACUM,
        (HILAZA_ACUM [FETCHA] ) = MAX (HILAZA_ACUM [FETCHA] )))

You could just modify it further to achieve what you want. You just have to get the the first data with regards to the date in the table, if that's what you meant by 'first' data. 

Hope this helps!