Get value of partner ticker

[oanu]

Hi all,

I would like to get the latest value of “Counterpart” ticker (aka “Peer”) of the selected ticker. Below are the details:

Model has 4 tables: Dim_Assets contains some metadata for tickers; Peers table specify which Ticker has which Peer (Peer is also Ticker Code from Dim_Assets); Historical Data is obvious.

All Ticker Codes have historical data. Several Ticker code could have the same Peer.

I have some measures which work just fine:

Current Value = CALCULATE(SUM('Fact_Historical_Data'[Value]),LASTDATE('Fact_Historical_Data'[Period]))

Date of Current Value = CALCULATE(SUM('Fact_Historical_Data'[Period]),LASTDATE('Fact_Historical_Data'[Period]))

Peer Existence Check = 
VAR CurrentTicker = SELECTEDVALUE(Dim_Assets[Ticker Code])
RETURN LOOKUPVALUE(Peers[Peer Code], Peers[Ticker Code], CurrentTicker, Blank())

Peer Latest Period = 
VAR CurrentTicker = SELECTEDVALUE(Dim_Assets[Ticker Code])
VAR PeerTicker = LOOKUPVALUE(Peers[Peer Code], Peers[Ticker Code], CurrentTicker, BLANK())
RETURN
IF(NOT ISBLANK(PeerTicker),
CALCULATE(MAX(Fact_Historical_Data[Period]), Fact_Historical_Data[Ticker Code] = PeerTicker, ALL(Fact_Historical_Data)),
BLANK()
)

However, the measure to get the value of Peer always returns blank:

Peer Current Value = 
VAR LatestPeriod = CALCULATE(MAX(Fact_Historical_Data[Period]), ALL(Fact_Historical_Data))
VAR CurrentTicker = SELECTEDVALUE(Dim_Assets[Ticker Code])
VAR PeerTicker = LOOKUPVALUE(Peers[Peer Code], Peers[Ticker Code], CurrentTicker, BLANK())
RETURN
IF(ISBLANK(PeerTicker), BLANK(),
    CALCULATE(
        SUM(Fact_Historical_Data[Value]),
        Fact_Historical_Data[Ticker Code] = PeerTicker,
        Fact_Historical_Data[Period] = LatestPeriod
    )
)

Could you pls give a hint here.

regards,

Alex

[oanu]

Hi all,

Thank you for support. I decided to move column "Peer Code" to Dim_Assets Table and also adjusted the "Peer Current Value" measure as follows:

Peer Current Value = 
VAR LatestPeriod = LASTDATE('Fact_Historical_Data'[Period])
VAR PeerTicker = SELECTEDVALUE(Dim_Assets[Peer Code])
RETURN
IF(
    ISBLANK(PeerTicker),
    BLANK(),
    CALCULATE(
        SUM(Fact_Historical_Data[Value]),
        All(Fact_Historical_Data),
        Fact_Historical_Data[Ticker Code] = PeerTicker,
        Fact_Historical_Data[Period] = LatestPeriod
    )
)

So I added filter "All(Fact_Historical_Data)" and now it works.

regards,

Alex

[WinterMist]

@oanu 

I'm wondering if this has to do with the data.

What happens if you remove the Alternate_Result parameter from VAR PeerTicker and simply return the VAR PeerTicker?

Your ALTERNATE RESULT = BLANK().

If it throws an error, that would tell me that more than one value is getting returned.

(And if you're getting more than one value, that makes sense why the entire measure is returning BLANK all the time.)

Regards,

Nathan

[WinterMist]

@oanu 

Is there any chance you can upload the PBIX to Google Drive and share the link in this thread?

Regards,

Nathan

P.S.  If not, would it be possible to create a PBIX with a small amount of mock data that should work?