\n
Correct Result - Screenshot(2)\n
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.
\nCurrentYear Formula
\n\n","author":{"@type":"Person","name":"Babulal"},"upvoteCount":0,"answerCount":2,"acceptedAnswer":{"@type":"Answer","text":"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-
\nNBRevenue = \nCALCULATE(\n SUM('AST DMNewLostMeasures'[Revenue]),\n FILTER(\n 'AST DMNewLostMeasures',\n 'AST DMNewLostMeasures'[MeasureType] = "NewRevenue/Policies"\n )\n)\n
\nYou can also change your CurrentYear measure as below-
\nCurrentYear = \nCALCULATE(\n MAX('AST DMNewLostMeasures'[Year]),\n FILTER(\n ALL('AST DMNewLostMeasures'),\n 'AST DMNewLostMeasures'[MeasureType] = "NewRevenue/Policies"\n )\n)\n
\n","author":{"@type":"Person","name":"mkRabbani"},"upvoteCount":1}}}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: 1123
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: 16918
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