Problems with LookupValue

[Matthew_Theis]

Hello Everyone,

Thanks for the help in advance.  I'm trying to utilize LOOKUPVALUE to provide me with the previous weeks' price for any given PartNumber and Distributor.  I don't want to hardcode in a distributor name, as I have done here, but if I leave the line as follows:
'Octopus FindChipsExport'[Distributor],'Octopus FindChipsExport'[Distributor],

"I get an error "a table of multiple values was supplied where a single value was expected."

Below is what I can get returned when I hardcode TTI in as a distributor...it provides the previous weeks' price for both distributors.

I am hoping to be able to achieve a column that can read the Distributor and PartNumber and provide me the previous weeks' price.  For example, on 18/08/09 Arrow Electronics Previous Weeks Price should read $0.2791 while TTI would read $0.0000.

Also, if you can help me understand what DAX is doing that would be great.

Thanks,

Matthew

[v-shex-msft]

Hi @Matthew_Theis,

>>a table of multiple values was supplied where a single value was expected

This error will appear when your formula return multiple values on one row. Please use calculate function to filter table and summary result.

Preview price =
CALCULATE (
    SUM ( Table[Price] ),
    FILTER (
        ALL ( Table ),
        Table[PartNumber] = EARLIER ( [PartNumber] )
            && Table[Distributor] = EARLIER ( [Distributor] )
            && Table[DateOfImport]
                = EARLIER ( [DateOfImport] ) - 7
    )
)

Regards,
Xiaoxin Sheng

[Matthew_Theis]

Hi @v-shex-msft thanks for the reply.  I tried your DAX and it appears that EARLIER isn't able to identify the previous row context, as can be seen by the error above.  

I did come up with something else that may work, much simplier.  

Prior Price =
CALCULATE(
           MAX('Octopus FindChipsExport'[Price]),
           'Octopus FindChipsExport'[DateOfImport]-7
)

Ideally I would be able to have this return blank for prices of 18/08/02 as there is no data to compare to prior to that date.  Are they downsides to this equation, should we be unable to get the one with EARLIER to work properly?

Thank you!

Matthew

[Matthew_Theis · Accepted solution]

I was able to come up with a solution to my problem.  Here is the query that I used...

Previous Price =
CALCULATE(
SUM('Octopus FindChipsExport'[Price]),
FILTER(
ALLSELECTED('Octopus FindChipsExport'),
'Octopus FindChipsExport'[DateOfImport] = SELECTEDVALUE('Octopus FindChipsExport'[DateOfImport]) - 7
&& 'Octopus FindChipsExport'[Distributor] = SELECTEDVALUE('Octopus FindChipsExport'[Distributor])
&& 'Octopus FindChipsExport'[PartNumber] = SELECTEDVALUE('Octopus FindChipsExport'[PartNumber])
)
)