Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate - Use formula in a Filter

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?

  • Anonymous's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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