Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with filter inside iterator measure

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!
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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] ) )
    )

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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] ) )
    )