Reputation: 369
I have created NBRevenue Measure to get SUM of Revenue with two conditions as filter as per below screen shot, The issue is when i add [CurrentYear] then the data displayed in below screenshot(1) is wrong but when I hard code 2020 in screenshot(2) then data is correct.
I did check that the [CurrentYear] is whole number and the table 'AST DMNewLostMeasures'[Year] is too whole number.
Correct Result - Screenshot(2)
I need to make this dynamic and hence please let me know what wrong I am doing or any other approach/suggestion would be great.
CurrentYear Formula
Upvotes: 0
Views: 1118
Reputation: 40244
Assuming your CurrentYear
measure returns what you expect, I'd recommend setting it as a variable rather than recalculating it for every row in your filter table:
NBRevenue =
VAR CurrYear = [CurrentYear]
RETURN
CALCULATE(
SUM('AST DMNewLostMeasures'[Revenue]),
FILTER(
'AST DMNewLostMeasures',
'AST DMNewLostMeasures'[MeasureType] = "NewRevenue/Policies"
&& 'AST DMNewLostMeasures'[Year] = CurrYear
)
)
Upvotes: 0
Reputation: 16908
I think you should use a Year slicer rather than using a Measure reference inside your formula. That will make everything dynamic and result will be populated as per selected Year in the slicer. Your Revenue code should be as below-
NBRevenue =
CALCULATE(
SUM('AST DMNewLostMeasures'[Revenue]),
FILTER(
'AST DMNewLostMeasures',
'AST DMNewLostMeasures'[MeasureType] = "NewRevenue/Policies"
)
)
You can also change your CurrentYear measure as below-
CurrentYear =
CALCULATE(
MAX('AST DMNewLostMeasures'[Year]),
FILTER(
ALL('AST DMNewLostMeasures'),
'AST DMNewLostMeasures'[MeasureType] = "NewRevenue/Policies"
)
)
Upvotes: 1