Enrich a table with transactions looking at the table itself

[sjoerdbuis]

Hi , I have a starting table with data (see below)

Now in this table I need to add calculated rows based on that table.

These rows need to be added every quarter

Any suggestions or any suggestions on the model used?

[v-yulgu-msft · Accepted solution]

Hi @sjoerdbuis ,

Add a calculated column in Start Table.

Column1 =
VAR previousdate =
    CALCULATE (
        MAX ( 'Start Table'[Date] ),
        FILTER (
            ALL ( 'Start Table' ),
            'Start Table'[Date] < EARLIER ( 'Start Table'[Date] )
                && 'Start Table'[Company] = EARLIER ( 'Start Table'[Company] )
        )
    )
RETURN
    [Amount]
        - CALCULATE (
            SUM ( 'Start Table'[Amount] ),
            FILTER (
                ALL ( 'Start Table' ),
                'Start Table'[Company] = EARLIER ( 'Start Table'[Company] )
                    && [Date] = previousdate
            )
        )

Create a calculated table.

End Table =
UNION (
    SELECTCOLUMNS (
        'Start Table',
        "Company", 'Start Table'[Company],
        "Sector", 'Start Table'[Sector],
        "Deal Type", 'Start Table'[Deal Type],
        "Amount", 'Start Table'[Amount],
        "Date", 'Start Table'[Date],
        "Transactor", 'Start Table'[Transactor],
        "Type2", 'Start Table'[ Type2]
    ),
    SELECTCOLUMNS (
        FILTER ( 'Start Table', 'Start Table'[Date] = MAX ( 'Start Table'[Date] ) ),
        "Company", 'Start Table'[Company],
        "Sector", 'Start Table'[Sector],
        "Deal Type", 'Start Table'[Deal Type],
        "Amount", 'Start Table'[Column1],
        "Date", 'Start Table'[Date],
        "Transactor", "Revalue",
        "Type2", "Revaluation"
    )
)

Best regards,

Yuliana Gu

[Michiel]

Is there a specific reason for wanting to add rows to the table? If you want to calculate the Revaluation, it is a rather straightforward time intelligence calculation:

Valuation = SUM('Start Table'[Amount])

Valuation Last Quarter = CALCULATE([Valuation], DATEADD(Dates[Date], -3; MONTH))

Revaluation = [Valuation] - [Valuation Last Quarter]

Make sure to hookup 'Start Table' to a date table (Dates, in my example).