Cars- how to evaluate First date seen + Price and also lastest date + price

[jr3151006]

Hi,

given that table, just created for example, we need:

1) list as distinct the 'Car' collumn but, based on it's 'Date' collumn, ordering by the lastest 10 records;

2) For each 'car', we need get the first 'date' + price; also get the lastest date + price in order to draw a graph for price evolution.

Any help on best strategies to accomplish that will be appreciated.

Table

####

----

Desired results from the source table + Desired Graph 

########################################

[v-yangliu-msft · Accepted solution]

Hi  @jr3151006 ,

Here are the steps you can follow：

Desired results from the source table 

1. Create measure.

First Value =
var _mindate=MINX(FILTER(ALL(TableCarsamdPrices),'TableCarsamdPrices'[Car]=MAX('TableCarsamdPrices'[Car])),[Date])
return
CALCULATE(SUM('TableCarsamdPrices'[Value]),FILTER(ALL(TableCarsamdPrices),'TableCarsamdPrices'[Car]=MAX('TableCarsamdPrices'[Car])&&'TableCarsamdPrices'[Date]=_mindate))

LastValue =
var
_maxdate=MAXX(FILTER(ALL(TableCarsamdPrices),'TableCarsamdPrices'[Car]=MAX('TableCarsamdPrices'[Car])),[Date])
return
CALCULATE(SUM('TableCarsamdPrices'[Value]),FILTER(ALL(TableCarsamdPrices),'TableCarsamdPrices'[Car]=MAX('TableCarsamdPrices'[Car])&&'TableCarsamdPrices'[Date]=_maxdate))

2. Result:

Desired Graph :

1. Using Enter data, create a table.

2. Create calculated column.

Value =
SWITCH(
    TRUE(),
    'Table'[First_Last]="First Value", CALCULATE(SUM('TableCarsamdPrices'[Value]),FILTER(ALL(TableCarsamdPrices),'TableCarsamdPrices'[Date]=
    MINX(FILTER(ALL('TableCarsamdPrices'),[Car]=EARLIER('Table'[Car])),[Date])&&'Table'[Car]=EARLIER('Table'[Car]))),
    'Table'[First_Last]="Lastest Value",  CALCULATE(SUM('TableCarsamdPrices'[Value]),FILTER(ALL(TableCarsamdPrices),'TableCarsamdPrices'[Date]=
    MaxX(FILTER(ALL('TableCarsamdPrices'),[Car]=EARLIER('Table'[Car])),[Date])&&'Table'[Car]=EARLIER('Table'[Car]))))

3.Result.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Whitewater100]

Hello:

 For First Date and Entry questions, I'll assume the table is named table. I don't think you have a date calendar and it really would be a great idea. With this in mind, 

First Date = FIRSTNONBLANK(Table[Date].[Date], SUM(Table[Value]))

First Date = FIRSTNONBLANKVALUE(Table[Date].[Date], SUM(Table[Value]))

and do the same thing for LASTNONBLANK & LASTNONBLANKVALUE.

I hope this works for you.

[jr3151006]

Can I use 'Power Query M' to reference the source table, group by 'car' and them discover the first value and the lastest value?

[amitchandak]

@jr3151006 , Try measures like

First = Calculate(firstnonblank(Table[Date],Max(Table[Value])))

Last = Calculate(lastnonblank(Table[Date],Max(Table[Value])))

or

First = Calculate(firstnonblank(Table[Date],Max(Table[Value])), allexcept(Table, Table[Car]))

Last = Calculate(lastnonblank(Table[Date],Max(Table[Value])), allexcept(Table, Table[Car]))

[jr3151006]

Hi @amitchandak,

tks for reply, but that DAX indiscriminate the 'car' and just collect the old one and new records.