URL link in values not working on previous year's values - why?

[Mjolnir]

Hi,

A matrix displaying values from measures [expenses_current_year] and [expenses_previous_year]; where both measures are formatted to retrieve URL link to the related invoice, through the measure [URL link] below. It works properly with the measure [expenses_current_year] but fail to retrieve the URL link for values from [expenses_previous_year]. Years are stored as text in my data-sets. I tried to create a measure separately for previous year [URL link previous year expenses] but without any success. 

Can someone please guide me on how to solve this matter?

IF (
    ISFILTERED ( fact_table[transactionid] ),
    CONVERT (
        CALCULATE (
            MAX ( dim_link[URL] ),
            FILTER (
                dim_orgdata,
                dim_orgdata[entity_id] = SELECTEDVALUE ( dim_orgdata[entity_id] )
            ),
            FILTER (
                dim_calendar,
                dim_calendar[year] = SELECTEDVALUE ( dim_calendar[year] )
                    || VALUE ( dim_calendar[year] )
                        = SELECTEDVALUE ( dim_calendar[year] ) - 1
            ),
            FILTER (
                fact_table,
                fact_table[transactionid] IN VALUES ( fact_table[transactionid] )
            )
        ),
        STRING
    ),
    BLANK ()
)

[Mjolnir]

Any ideas? Not managed to solve this yet.

[jupsimarsingh]

Try creating Separate Measures for Each Year and ensure Correct Filter Context .Take a look at this :
Current Year Expenses URL =
IF (
ISFILTERED ( fact_table[transactionid] ),
CALCULATE (
MAX ( dim_link[URL] ),
FILTER (
dim_orgdata,
dim_orgdata[entity_id] = SELECTEDVALUE ( dim_orgdata[entity_id] )
),
FILTER (
dim_calendar,
dim_calendar[year] = SELECTEDVALUE ( dim_calendar[year] )
),
FILTER (
fact_table,
fact_table[transactionid] IN VALUES ( fact_table[transactionid] )
)
),
BLANK ()
)




For Previous Year Expenses URL :
Previous Year Expenses URL =
IF (
ISFILTERED ( fact_table[transactionid] ),
CALCULATE (
MAX ( dim_link[URL] ),
FILTER (
dim_orgdata,
dim_orgdata[entity_id] = SELECTEDVALUE ( dim_orgdata[entity_id] )
),
FILTER (
dim_calendar,
dim_calendar[year] = VALUE ( SELECTEDVALUE ( dim_calendar[year] ) ) - 1
),
FILTER (
fact_table,
fact_table[transactionid] IN VALUES ( fact_table[transactionid] )
)
),
BLANK ()
)



Please mark this as the solution if it works for you!

[Mjolnir]

Thank's for your effort, using a separate measure for previous year's URLs as you suggested now returns the URL string when the measure is being added to a column in a visual table.

Though, when conditionally formatting returned values in a matrix to contain the URL for previous year, it does not work. 

I add the measure, "Actual amount previous year" to the value field in my matrixes, then under conditional formatting adding the URL, using the measure you suggest - no URL appearing in the values in the matrix. Though, your suggested measure returns the full URL string when added as a column to a visual table.

Could it be the measure for calculating previous year's actual amount that is causing the URL issue?

Actual amount previous year = 
    CALCULATE (
        SUM(fact_table[amount]),
        FILTER (
            ALL(dim_calendar),
            VALUE(dim_calendar[year]) = SELECTEDVALUE(dim_calendar[year]) - 1 &&
            dim_calendar[quarter] IN VALUES(dim_calendar[quarter]) &&
            dim_calendar[month] IN VALUES(dim_calendar[month])
        )
    )