Measure sum based on lookup-ed version

[vipett]

I would need a little help, I need to do a measure based on a certain version, the version needs to be looked up based on selected value in a slicer..

I have a table roughly like this called 'Standard'

Version	Product	Month	Amount
2212	A	2304	100
2301	A	2304	50

And a table called 'Version'

Version	Previous	Previous Previous
2212	2211	2210
2301	2212	2122

In my visualization I have a slicer to choose Version and in the matrix I want to show (if I have selected 2301 as the version)

Product	Month	Current amount	Previous amount
A	2304	50	100

The current amount is easy, but how do I calculate the previous amount? I tried something like 

Previous Forecast = CALCULATE(SUM(Standard[Amount]),FILTER(Standard,Standard[Version]=RELATED(Version[Previous]))) but it doesn't work..

Any ideas?

thanks!

[Anonymous · Accepted solution]

Hi @vipett ,

You can create a measure as below to get it, please find the details in the attachment.

Previous amount  = 
VAR _previous =
    CALCULATE (
        MAX ( 'Version'[Previous] ),
        FILTER ( 'Version', 'Version'[Version] = SELECTEDVALUE ( 'Standard'[Version] ) )
    )
RETURN
    CALCULATE (
        SUM ( 'Standard'[Amount] ),
        FILTER ( ALL ( 'Standard' ), 'Standard'[Version] = _previous )
    )

Best Regards