Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
97 | |
80 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |