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.

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    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.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , You can do that in new column using earlier

     

    sumx(filter(table , [AccNum] = earlier([AccNum]) && [Date] = earlier([Date])),[Total])

     

    Your logic is not clear. So not able to suggest the exact formula. You can treat earlier as new copy of same table

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      The logic is making an extra column that contains the totals from the previous month and then by subtracting them will find the difference (i.e. the movement inside the month) in order to use it for other calculations.