Looking for Last Value for each

[PaulPalkowski]

I have this near complete...

What would I use to get the "last value", or the value with the most recent date/time 

Each site has about 60,000 records

Do you need more info to help me on this?

[V-lianl-msft]

Hi @PaulPalkowski ,

Try to create a measure or column:

mostRecentDate = CALCULATE(MAX(table[LastDate]),ALLEXCEPT(table,table[Site])) 

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

[PaulPalkowski]

I have the last date... I need the VALUE from that last date..

I have Min Value, Max Value, Avg Value for each, just need a new column for the LAST value 

[PaulPalkowski]

here is what my data looks like

Looking for value of last date for each SITE and AREA

[V-lianl-msft]

Hi @PaulPalkowski ,

Try:

Measure = CALCULATE(LASTNONBLANK('Table'[VALUE],0),ALLEXCEPT('Table','Table'[SITE],'Table'[AREA]))

Column = CALCULATE(LASTNONBLANK('Table'[VALUE],0),ALLEXCEPT('Table','Table'[SITE],'Table'[AREA]))

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

[PaulPalkowski]

It gives me a value but not the most recent

[Pragati11]

Hi @PaulPalkowski ,

On what basis you need the last value?

Is it just date or along with date any categorical column in your data?

[PaulPalkowski]

The last value for the last date for each Site/Area

[Pragati11]

Hi @PaulPalkowski ,

You can create a column as follows:

Is most recent =
var thisSite = tablename[Site]
var thisArea = tablename[Area]
var mostRecentDate = CALCULATE(MAX(tablename[Date]),FILTER(ALL(tablename), tablename[Site] = thisSite && tablename[Area] = thisArea))
return
IF(tablename[Date] = mostRecentDate,"yes","no")

The above DAX will return a column with YES/NO value based on most recent record at Site and Area level. Move this column to your table visual and select "yes". This will just display recent record in the table for a combination of Site and Area.

You can modify the above code as just SITE level or AREA level also.

Replace the right table name and column name in the above dax provided.

Thanks,

Pragati

[PaulPalkowski]

Since I cannot see how this can be done in the same table

Is it possible to create a new table from the original table with the value for the last known date of each Site and Area

16 rows and then create a measure to do a lookup back to my table or matrix visual?

[PaulPalkowski]

I dont want the last record, I want the value of the last record and display that with my current table

I have a column of values....So I am looking to replace TBD with the value from the last date for each site