Help with filter inside iterator measure

[Anonymous]

Hi,
I have the following measure and works fine for month "8" or any other given month (changing the number on calculate filter), but I need make a new measure that instead of the given month, show the last available month so I don't have to change it manually every month.

Measure for month 8 = SUMX(

ADDCOLUMNS(

SUMMARIZE(

'DB asignaciones'

, 'DB asignaciones'[Month]

, 'DB asignaciones'[RBD]

, 'DB asignaciones'[YEAR]

)

, "PP" , CALCULATE( MAX( 'DB asignaciones'[Poblacion Potencial RBD]),'DB asignaciones'[Month]=8)

)

, [PP])

any help would be greatly appreciated!

[AlexisOlson · Accepted solution]

I think it makes more sense to filter before summarizing instead of after.

Try something like this:

Measure for last available month =
VAR LastAvailableMonth = MAX ( 'DB asignaciones'[Month] )
VAR LastAvailableYear  = MAX ( 'DB asignaciones'[YEAR] )
RETURN
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER (
                    'DB asignaciones',
                    'DB asignaciones'[Month] = LastAvailableMonth &&
                    'DB asignaciones'[YEAR]  = LastAvailableYear
                ),
                'DB asignaciones'[RBD]
            ),
            "PP", CALCULATE ( MAX ( 'DB asignaciones'[Poblacion Potencial RBD] ) )
        ),
        [PP]
    )

You can probably simplify it further like this:

Measure for last available month =
VAR LastAvailableMonth = MAX ( 'DB asignaciones'[Month] )
VAR LastAvailableYear  = MAX ( 'DB asignaciones'[YEAR] )
RETURN
    SUMX (
        CALCULATETABLE (
            VALUES ( 'DB asignaciones'[RBD] ),
            KEEPFILTERS ( 'DB asignaciones'[Month] = LastAvailableMonth ),
            KEEPFILTERS ( 'DB asignaciones'[YEAR]  = LastAvailableYear )
        ),
        CALCULATE ( MAX ( 'DB asignaciones'[Poblacion Potencial RBD] ) )
    )