Forum Discussion

Matthew_Theis's avatar
Matthew_Theis
Advocate II
8 years ago
Solved

Problems with LookupValue

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...
Show and Tell
Tutorial Requests
  • Matthew_Theis's avatar
    Matthew_Theis
    8 years ago

    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])
    )
    )

Matthew_Theis's avatar
Matthew_Theis
Advocate II
8 years ago

 

Hi Anonymous 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's avatar
Matthew_Theis
Advocate II
8 years ago

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])
)
)