Forum Discussion
Create a constant variable based in a filter and use this as baseline for every calculation
Hi everyone,
I've been trying to create a constant from a filter and doing a calculation based on this value, but I am unable to do so.
I would like to compare the % growth of the quantity sold based in a fixed fiscal year.
In this example, I would like to compare the % growth of all years following 2018/2019 with the quantity sold in that year.
Example:
Fiscal Year 2019 /2020 = (19.615 - 19.275) / 19.275 = 0,0176
Fiscal Year 2020 /2020 = (19.209- 19.275) / 19.209 = -0,0034
Before this baseline year, I would like to have empty values.
It should also be possible to filter per subsidiary.
So, I created a calculated column:
baseline year = calculate(sum('Fact Current Data'[History]), filter('Dim Date', 'Dim Date'[Fiscal Year] = "2018 /2019"))And then this metric:
% growth regarding to baseline year =
var baseline = sum('Fact Current Data'[baseline year])
RETURN
DIVIDE(
sum('Fact Current Data'[History])-baseline, baseline
)
But it is not working as I would like to. As you can see in the picture attached or the dummy file.
Could you please help? Thanks in advance!
Dummy file can be download in:
The baseline variable will get evaluated for each line in the visual.
I reckon you could use this:
var baseline = CALCULATE( sum('Fact Current Data'[baseline year]), REMOVEFILTERS('Dim Date'[Fiscal Year]))You'll still get values for the previous years to the baseline so you could filter those out
Try defining this as a measure:
baseline year = CALCULATE ( SUM ( 'Fact Current Data'[History] ), 'Dim Date'[Fiscal Year] = "2018 /2019" )Then this:
% growth relative to baseline year = VAR baseline = [baseline year] VAR curryear = SUM ( 'Fact Current Data'[History] ) RETURN DIVIDE ( curryear - baseline, baseline )You'll want to include something like IF ( MAX ( 'Fact Current Data'[Fiscal Year] ) < "2018 /2019", BLANK(), ... ) in order to blank out prior years but I'm not sure if that's the best way to write it. You generally want to do comparisons on number/date columns.
4 Replies
Try defining this as a measure:
baseline year = CALCULATE ( SUM ( 'Fact Current Data'[History] ), 'Dim Date'[Fiscal Year] = "2018 /2019" )Then this:
% growth relative to baseline year = VAR baseline = [baseline year] VAR curryear = SUM ( 'Fact Current Data'[History] ) RETURN DIVIDE ( curryear - baseline, baseline )You'll want to include something like IF ( MAX ( 'Fact Current Data'[Fiscal Year] ) < "2018 /2019", BLANK(), ... ) in order to blank out prior years but I'm not sure if that's the best way to write it. You generally want to do comparisons on number/date columns.
Hi AlexisOlson ,
thank you for the answer. It works! I can create a numeric variable to filter and create the blank spaces. Thank you for the suggestion!
The baseline variable will get evaluated for each line in the visual.
I reckon you could use this:
var baseline = CALCULATE( sum('Fact Current Data'[baseline year]), REMOVEFILTERS('Dim Date'[Fiscal Year]))You'll still get values for the previous years to the baseline so you could filter those out
HotChilli REMOVEFILTERS is exactly the function I needed. I tried with ALLEXCEPT but it was wrong. Thank you so much!
