Calculate - Use formula in a Filter

[Anonymous]

I'm calculating Year over Year percent differences, but my years are fiscal years, so while I can create a Measure that calculates the percent difference from a hard-coded year, I can't figure out how to make it work for any year.  Here's the Quick Measure I use:

ROAS %ΔFY18 =
VAR __BASELINE_VALUE = CALCULATE([ROAS], 'DateTable'[Fiscal Year] IN { "FY18" })
VAR __MEASURE_VALUE = [ROAS]
RETURN
IF(
NOT ISBLANK(__MEASURE_VALUE),
DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)
)

I'd like to be able to take FY18 and do something like ([Fiscal Year] - 1). I tried just putting a SUBSTITUTE formula where it says "FY18", but it won't let me reference the Fiscal Year column. Any ideas?

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can use dax functions 'left', 'right' and 'value' to achieve your requirement:

ROAS Previous =
VAR currYear =
    SELECTEDVALUE ( 'DateTable'[Fiscal Year] )
VAR __BASELINE_VALUE =
    CALCULATE (
        [ROAS],
        'DateTable'[Fiscal Year]
            = LEFT ( currYear, 2 )
                & VALUE ( RIGHT ( currYear, 2 ) ) - 1
    )
VAR __MEASURE_VALUE = [ROAS]
RETURN
    IF (
        NOT ISBLANK ( __MEASURE_VALUE ),
        DIVIDE ( __MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE )
    )

Regards,
Xiaoxin Sheng