Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add new column with filters based on current row values

My main question here is around how to filter based on a value taken from the current column.   In this case I have a column Dates.Month (which is a whole number in the format YYYYMM) and a new cal...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Try this:

    Column =
VAR LastMonth = 'Table'[Dates.BeforeMonth]
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Dates.Month] = LastMonth )
    )

    or,

    Column =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Dates.Month] = EARLIER ( 'Table'[Dates.BeforeMonth] )
    )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

Try this:

Column =
VAR LastMonth = 'Table'[Dates.BeforeMonth]
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Dates.Month] = LastMonth )
    )

or,

Column =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Dates.Month] = EARLIER ( 'Table'[Dates.BeforeMonth] )
    )
)

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks, I used the first one.  It was very similar to my code, but with the added "ALL('Table')" and an explicit FILTER (rather than implicit in the CALCULATE) - and that seems to have done the trick.