Getting the earliest value of a table visual

[Anonymous]

Hi,

I'm trying to create a dax measure for getting the earliest price of an item in a table visual and show that same value on every row.

However I'm having trouble in clearing the filter context of the table.

What I have now is this measure which works as a separate visual but not in a table visual:

FirstUNITPRICE =
CALCULATE (
    SUM ( Purchases[UNITPRICE] );
    FILTER ( ALL ( Purchases) ; Purchases[Date] = MIN ( Purchases[Date] ) )
)

What im trying to achieve is to show the same measure value of '92' in every row of the visual:

 

I have a separate date table which I use as a slicer and also have it as date column in the table visual:

Any help would be appreciated.

Example .pbix

[Zubair_Muhammad]

@Anonymous 

Try this one

FirstUNITPRICE =
CALCULATE (
    SUM ( Purchases[UNITPRICE] );
    FILTER ( ALL ( Purchases) ; Purchases[Date] = MIN ( DateTable[Date] ) )
)

[Anonymous]

Unfortunately the result is the same as above.

[Zubair_Muhammad]

@Anonymous 

And this one?

FirstUNITPRICE = 
VAR mydate= CALCULATE(MIN ( DateTable[Date] ),ALLSELECTED())
RETURN
CALCULATE (
    SUM ( Purchases[UNITPRICE] ),
    FILTER ( ALL ( Purchases[Date]) , DateTable[Date] = mydate )
)

[Anonymous]

That returns a value only for the first row only if slicer start date is same than the line date.

I have attached the example .pbix to my original post.

[Zubair_Muhammad]

@Anonymous 

This works with your file.

But I had to change Date from DateTable to Date from Purchases Table in your file

See the attached file please

FirstUNITPRICE = 
VAR my_date =
    CALCULATE (
        MIN ( Purchases[Date] ),
        FILTER ( ALL ( Purchases ), Purchases[Date] > MIN ( DateTable[Date] ) ),VALUES(Purchases[ItemID])
    )
RETURN
    CALCULATE (
        SUM ( Purchases[UNITPRICE] ),
        Purchases[Date] = my_date,
        ALL ( purchases )
    )

[Anonymous]

I have a more complex report where I'm having this problem and that data model requires me to use the date column of separate date table. I'm trying to figure out if there is a way to do this while using the DateTable[Date] column.

[Zubair_Muhammad · Accepted solution]

@Anonymous 

We can use this with DATETABLE{Date]

First_UNITPRICE =
VAR my_date =
    MINX (
        ALLSELECTED ( DateTable ),
        CALCULATE (
            MIN ( Purchases[Date] ),
            ALLEXCEPT ( Purchases, DateTable[Date] ),
            VALUES ( Purchases[ItemID] )
        )
    )
RETURN
    CALCULATE (
        SUM ( Purchases[UNITPRICE] ),
        Purchases[Date] = my_date,
        ALL ( purchases )
    )

[Zubair_Muhammad]

@Anonymous 

Your file attached as well with above and previous measures