Forum Discussion

Matren's avatar
Matren
Frequent Visitor
2 years ago
Solved

DAX Last Record Value

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

 

 

7 Replies

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      Slight variation:

       

       

       

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

       

       

    • Matren's avatar
      Matren
      Frequent Visitor
      2 years ago

      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's avatar
        Matren
        Frequent Visitor
        2 years ago

        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's avatar
      Matren
      Frequent Visitor
      2 years ago

      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