Forum Discussion

queenbean84's avatar
queenbean84
Copper Contributor
Jul 12, 2023

Income Statement by month using pivot table

I am trying to create an income statement by month for the attached file.  It will not allow me to group by month.  I also want to have a grand total for PROFIT OR (LOSS).  Any help here would be app...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 13, 2023

queenbean84 

That could be done using data model if your Excel supports it.

As variant:

- add Transactions table to data model (better by Power Query)

- create 'Calendar' (aka Date) table in Power Pivot, however it's better to generate by Power Query from scratch, lot of templates for that

- create another table for Groups adding Profit (Loss) item to it (again better with Power Query)

- create relationships 

- hide related fields on "many" site of relationship

- create measures

Total Amount:=SUMX ( Data, Data[Amount] * IF ( Data[Group] = "Income", 1, -1 ) )

Total:=SUMX (
    'Group',
    IF (
        'Group'[Group] = "Profit (Loss)",
        IF (
            ISFILTERED ( Data[Category] ),
            BLANK (),
            CALCULATE (
                [Total Amount],
                ALLEXCEPT ( Data, Data[Category], 'Calendar'[Month], Data[Property] )
            )
        ),
        [Total Amount]
    )
)

above is assumed you don't need P&L for the category

- create PivotTable from data model

The rest is cosmetic.

Please check in attached file.

Resources