Lookup values in one column

Hi Anonymous ,

I think you don't need to transform your table in Powre Query. It would be memory intensive. I suggest you to create a DimDate table and create measure to achieve your goal.

DimDate = CALENDARAUTO()

Relationship:

Measures:

Measure = 
VAR _LIST = CALCULATETABLE(VALUES('Table'[Creation file date]),ALL('Table'))
RETURN
IF(MAX(DimDate[Date]) IN _LIST,CALCULATE(COUNT('Table'[Creation file date]))+0)

Count of Add = 
VAR _ADD =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( 'Table'[Product ID] ), ALL ( 'Table' ) ),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    COUNTAX ( FILTER ( _ADD, [StartDate] = MAX ( DimDate[Date] ) ), [Product ID] )

ID of Add = 
VAR _ADD =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( 'Table'[Product ID] ), ALL ( 'Table' ) ),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    CONCATENATEX (
        FILTER ( _ADD, [StartDate] = MAX ( DimDate[Date] ) ),
        [Product ID],
        " "
    )

Count of Remove = 
VAR _ADD =
    ADDCOLUMNS (
       CALCULATETABLE( VALUES ( 'Table'[Product ID] ),ALL('Table')),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    COUNTAX(FILTER(_ADD,[EndDate] =MAX(DimDate[Date])),[Product ID])

ID of Remove = 
VAR _ADD =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( 'Table'[Product ID] ), ALL ( 'Table' ) ),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    CONCATENATEX (
        FILTER ( _ADD, [EndDate] = MAX ( DimDate[Date] ) ),
        [Product ID],
        " "
    )

Result is as below.

Best Regards,
Rico Zhou

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