Custom operation in groupby

Hi  Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Year =
YEAR('Table'[logic_date])

2. Create measure.

Measure_goss =
VAR _yeartoday =
    YEAR ( TODAY () )
VAR _table1 =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Year] = _yeartoday
                    || 'Table'[Year] = _yeartoday - 1
                    || 'Table'[Year] = _yeartoday + 1
                    || 'Table'[Year] = _yeartoday + 2
            ),
            "1", [Year]
        )
    )
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[gross] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[equipment model name] = MAX ( 'Table'[equipment model name] )
                && 'Table'[ship-to customer name] = MAX ( 'Table'[ship-to customer name] )
                && 'Table'[Year] = MAX ( 'Table'[Year] )
                && 'Table'[Year] IN _table1
        )
    )
RETURN
    IF (
        NOT ( ISINSCOPE ( 'Table'[ship-to customer name] ) )
            && ISINSCOPE ( 'Table'[equipment model name] ),
        CALCULATE (
            SUM ( 'Table'[gross] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[equipment model name] = MAX ( 'Table'[equipment model name] )
                    && 'Table'[Year] = MAX ( 'Table'[Year] )
                    && 'Table'[Year] IN _table1
            )
        ),
        IF (
            ISINSCOPE ( 'Table'[ship-to customer name] ),
            _sum,
            CALCULATE (
                SUM ( 'Table'[gross] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Year] = MAX ( 'Table'[Year] )
                        && 'Table'[Year] IN _table1
                )
            )
        )
    )

Measure_net =
VAR _yeartoday =
    YEAR ( TODAY () )
VAR _table1 =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Year] = _yeartoday
                    || 'Table'[Year] = _yeartoday - 1
                    || 'Table'[Year] = _yeartoday + 1
                    || 'Table'[Year] = _yeartoday + 2
            ),
            "1", [Year]
        )
    )
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[net] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[equipment model name] = MAX ( 'Table'[equipment model name] )
                && 'Table'[ship-to customer name] = MAX ( 'Table'[ship-to customer name] )
                && 'Table'[Year] = MAX ( 'Table'[Year] )
                && 'Table'[Year] IN _table1
        )
    )
RETURN
    IF (
        NOT ( ISINSCOPE ( 'Table'[ship-to customer name] ) )
            && ISINSCOPE ( 'Table'[equipment model name] ),
        CALCULATE (
            SUM ( 'Table'[net] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[equipment model name] = MAX ( 'Table'[equipment model name] )
                    && 'Table'[Year] = MAX ( 'Table'[Year] )
                    && 'Table'[Year] IN _table1
            )
        ),
        IF (
            ISINSCOPE ( 'Table'[ship-to customer name] ),
            _sum,
            CALCULATE (
                SUM ( 'Table'[net] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Year] = MAX ( 'Table'[Year] )
                        && 'Table'[Year] IN _table1
                )
            )
        )
    )

3. Result:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Liu Yang

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