CODEWITHSUNDEEP
powerbi
Babulal
Babulal

Reputation: 369

Calculated measure not working with DAX filter

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.

Wrong Result- ScreenShot(1) enter image description here

Correct Result - Screenshot(2) enter image description here

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

enter image description here

Upvotes: 0

Views: 1118

Answers (2)

Alexis Olson
Alexis Olson

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

mkRabbani
mkRabbani

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

Related Questions