Fetch dimension value dynamically based on Filter Context

[Anonymous]

Hi,

I am trying to fetch the corresponding title from last year onto a row with a new title this year.

Example is we have rows with Sales and Sales LY and then we want the Title and Title LY for that. How can I fetch the Title LY?

I added a picture as an example. The top is how it might look like in a table and the bottom is how I want the result to be.

I thought maybe I could do something like:

Title (LY) =

VAR vMaxDateLastYear = YEAR ( [MaxDate] ) - 1

VAR TitleID= MIN ( dim_issue[TitleID] )

RETURN

CALCULATE (
MIN ( dim_issue[Title] );
FILTER ( dim_issue; dim_issue[TitleID] = Utgavenummer );
dim_time[Year] = vMaxDateLastYear
)

Thanks for any replies.

Best,

Ali A

[v-xuding-msft]

Hi @Anonymous ,

I created a calculated column with the function of EARLIER that you have a try.

Last Title = CALCULATE(MAX('Table'[Title]),FILTER('Table','Table'[Title ID] = EARLIER('Table'[Title ID]) && 'Table'[Year] +1 = EARLIER('Table'[Year])))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xuding-msft 

This seems like it is correct, but I am having some issues using it in my code. I suspect that it is because it is not in the same model.

[Anonymous]

So after trying some back and forth what did it was including a CrossFilter!

Title (Current Year) =
VAR vCurrentYearBasedOnMaxDate =
YEAR ( [Last Chosen Date] )
VAR Utgavenummer =
MIN ( dim_issueTitleID)
RETURN
CALCULATE (
MIN ( dim_issueTitle);
dim_time[Year] = vCurrentYearBasedOnMaxDate;
CROSSFILTER ( dim_issue[pk_dim_issue]; fact_issue[fk_dim_issue]; BOTH )
)