Reply
Frequent Visitor
Posts: 13
Registered: ‎07-25-2016
Accepted Solution

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?


Accepted Solutions
Highlighted
Community Support Team
Posts: 7,479
Registered: ‎08-14-2016

Re: Calculate - Use formula in a Filter

Hi @Nathan_W,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post


All Replies
Highlighted
Community Support Team
Posts: 7,479
Registered: ‎08-14-2016

Re: Calculate - Use formula in a Filter

Hi @Nathan_W,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |