Merge Table by itself

[Anonymous]

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.

 

[v-alq-msft · Accepted solution]

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]

@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]

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.

[Pragati11]

Hi @Anonymous ,

I would suggest doing this in Power BI Query Editor.

Duplicate your table and the choose Merge for these tables. 

Refer following article on how to merge tables in Power BI:

https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query

Thanks,

Pragati