DAX Last Record Value

[Matren]

Hello Everyone,

How could I write a DAX expression with existing columns (Date, Product, Value) to generate a new column (Last Record)?

Basically, it is taking the last Value for a Product and placing it in every value for Last Record corresponding to that Product.

Thanks in Advance!

Matren

[ThxAlot · Accepted solution]

[lbendlin]

Slight variation:

Last = SELECTCOLUMNS(INDEX(-1,ALL('DATA'),PARTITIONBY([Product]),MATCHBY([Date])),[Value])

[Matren]

Hi @ThxAlot ,

Thanks! I think this looks right.

I will check out the solution with my implementation and then accept the solution.

Have a good day,

Matren

[Matren]

Hi @ThxAlot ,

I was able to add sparklines showing the trend over time for products.

It would be great if Microsoft added "Last" to the summation, instead of having to write DAX code to produce sparklines showing how a value changes over time.

Thanks Again!

[Matren]

Hi @ThxAlot , @lbendlin , @Charles_625g 

Please vote for this idea to add LAST in the summation for sparklines:

https://community.fabric.microsoft.com/t5/Custom-Visuals-Ideas/Add-LAST-to-summation-for-Sparklines/... 

Thanks,

Matren

[lbendlin]

Does it have to be DAX or is Power Query an option too?

[Matren]

Hi @lbendlin,

I need to create a new column (Last Record) so I can display it in a report showing sparklines which display the value for my company's products. These values go up and down throughout the year and I want to show the latest value with a sparkline that shows the trend over time. In order to do the sparkline, it asks for the value to be summarized as a (sum, minimum, maximum, count, median, variance, etc) -- but no last value.

I wish to generate a new column (Last Record) with the values shown in the screenshot of the spreadsheet. However this is done (i.e using DAX or Power Query) doesn't matter as long as it works and isn't hard-coded.

Thanks,

Matren