Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Merge Table by itself

Hi all!   I want to create this table. Merge a table with the same table in order to create an extra column that is based on two keys: AccNum and the Date.    
Need Help
Show and Tell
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attacehd in the end.

    Table:

     

    You may create a calculated column as below.

    Result = 
var _lastvalue = 
CALCULATE(
    SUM('Table'[Total]),
    FILTER(
        'Table',
        'Table'[AccNum]=EARLIER('Table'[AccNum])&&
        'Table'[Date]=EARLIER('Table'[PreviousDate])
    )
)
return
COALESCE(_lastvalue,0)

     

    Result:

     

    Or you can create a calculated table as below.

    NewTable = 
ADDCOLUMNS(
    'Table',
    "Result2",
    var _lastvalue = 
    CALCULATE(
        SUM('Table'[Total]),
        FILTER(
            'Table',
            'Table'[AccNum]=EARLIER('Table'[AccNum])&&
            'Table'[Date]=EARLIER('Table'[PreviousDate])
        )
    )
    return
    COALESCE(_lastvalue,0)
)

     

    Result:

     

    Best Regards

    Allan

     

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