CODEWITHSUNDEEP
powerpivotdax
user4815740
user4815740

Reputation: 311

DAX - Last Value

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?

Upvotes: 10

Views: 16635

Answers (3)

Alexis Olson
Alexis Olson

Reputation: 40264

With the new window functions in DAX, I think this can be simplified to

Last Traded Value =
CALCULATE (
    MAX ( 'Table'[Traded Value] ),
    INDEX (
        1,
        ORDERBY ( 'Table'[Time], DESC ),
        PARTITIONBY ( 'Table'[Date] )
    )
)

Upvotes: 0

Jason Wang
Jason Wang

Reputation: 51

For example:

DEFINE
VAR TableTMP =
    ADDCOLUMNS ( 'Table', "DateTime", [Date] + [Time] )
EVALUATE
    SUMMARIZE (
      NATURALINNERJOIN (
        TableTMP,
        SUMMARIZE  (
          GROUPBY ( TableTMP, [Date], "DateTime", MAXX ( CURRENTGROUP (), [DateTime] ) ),
          [DateTime]
        )
      ),
      [Date],
      [Time],
      [Traded Value]
    )

Upvotes: 1

alejandro zuleta
alejandro zuleta

Reputation: 14108

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.

Upvotes: 13

Related Questions