FIltering max date

[Anonymous]

Hi,

For some reason I cannot filter sum so it only takes the biggest date. The measure returns me the whole total of all months, not the most recent one, not sure why. Any idea how I could fix that? Many thanks

CurrentWO = CALCULATE(sum('June WO`s'[Journal Amount]), FILTER(ALL('June WO`s'[Period]) , 'June WO`s'[Period] = MAX('June WO`s'[Period],'June WO`s'[Period])))

[Anonymous · Accepted solution]

Of course, it returns everything. FILTER is an iterator (do you know what that means?). This code

    FILTER (
        ALL ( 'June WO`s'[Period] ),
        'June WO`s'[Period] = MAX ( 'June WO`s'[Period], 'June WO`s'[Period] )
    )

iterates all distinct values of 'June WO`s'[Period] and for each row calculates the condition:

'June WO`s'[Period] = MAX ( 'June WO`s'[Period], 'June WO`s'[Period] )

What's the result of 

MAX ( 'June WO`s'[Period], 'June WO`s'[Period] )?

Well, it's nothing else than

'June WO`s'[Period].

And what's the value of the condition

'June WO`s'[Period] = MAX ( 'June WO`s'[Period], 'June WO`s'[Period] )?

Well, it's TRUE. ALWAYS, since 

'June WO`s'[Period] = 'June WO`s'[Period]

Hence, your filter does not filter anything. It returns all distinct values of 'June WO`s'[Period]. To understand code, I'd suggest you learn something about DAX.

Your code should probably be this:

CurrentWO =
var __maxPeriodInCurrentContext = MAX( 'June WO`s'[Period] )
var __result =
CALCULATE (
    SUM ( 'June WO`s'[Journal Amount] ),
    'June WO`s'[Period] = __maxPeriodInCurrentContext
)
return
    __result

Please learn DAX. If you don't, you'll be producing numbers you will not be able to understand and will not know where they come from.

Best

Darek

Best

Darek