DAX - Last Value

user4815740 picture user4815740 · Nov 25, 2016 · Viewed 11.3k times · Source

I have this table

enter image description here

I would like to create measurement get the last traded value for each day. E.g.

enter image description here

How the DAX query should look like?

Answer

alejandro zuleta picture alejandro zuleta · Nov 25, 2016

You have to create two measures. One for the last time in each date and another to get the value for that date and time.

Last Time := 
CALCULATE(MAX([Time]),FILTER('Table',[Date]=MAX([Date])))

Last Traded Value =
    CALCULATE (
        MAX ( 'Table'[Traded Value] ),
        FILTER ( 'Table', [Date] = MAX ( [Date] ) && [Last Time] = [Time] )
    )

Then add Date column to rows and Last Time and Last Traded Value measures to Values pane in a pivot table.

Let me know if this helps.