Forum Discussion
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?
- Anonymous7 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
- AnonymousNot 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