Dax calculated column, vlookup max value

0
votes

The goal is for the power pivot table to only show pricing information that corresponds with the last time stamp, all necessary data is in one table.

In normal excel, I would try to solve the problem with a vlookup.

I have tried the below calculated column (along with others) which did not work. The results is that it reflects the time stamp for that row, not the max time of that day, which then does not provide the desired results on the power pivot.

column =
CALCULATE ( MAX ( TabName[Effective Time] ), ALL ( TabName ), TabName[Result] )
    = MAXX (
        FILTER (
            TabName,
            EARLIER ( TabName[Effective Time] ) = TabName[Effective Time]
        ),
        TabName[Result]
    )

Here is a example of the output I'm looking for.

For the blue paint product there are two times, 5:00 and 17:00.

It finds the latest time (17:00) and outputs it in the calculated column.

Then repeats grabbing the latest price for each individual product.

Date Effective Time Product Price Desired Calculated Column
7/13/2021 5:00 Blue Paint 2.00 17:00
7/13/2021 12:00 Green Paint 3.00 16:00
7/13/2021 17:00 Blue Paint 3.00 17:00
7/13/2021 16:00 Green Paint 2.00 16:00
7/13/2021 5:00 Red Paint 4.00 11:00
7/13/2021 11:00 Red Paint 4.00 11:00
2
excelpowerbidax

2 Answers

1
votes

The ALLEXCEPT function is handy for these kinds of situations:

Desired Calculated Column =
CALCULATE (
    MAX ( TabName[Effective Time] ),
    ALLEXCEPT ( TabName, TabName[Product], TabName[Date] )
)

This removes all filter context except for the columns specified.

0
votes
Column = CALCULATE(MAXX('fact','fact'[Effective Time]),ALLEXCEPT('fact','fact'[Product]))

Solution

If you need to the max of Effective Time by Date-Product grouping use the following

Column = CALCULATE(MAXX('fact','fact'[Effective Time]),ALLEXCEPT('fact','fact'[Product],'fact'[Date]))