## Desktop

Frequent Visitor
Posts: 13
Registered: ‎07-25-2016

# 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: | |

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: | |