Income Statement by month using pivot table

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.

Hi queenbean84 

You can't group the date field because you have a calculated item on the Group field. 

I downloaded a copy of your file, deleted the PROFIT+LOSS item from the Group field, and afterwards I was able to group the date field. 

Unfortunately, it seems you can't have a calculated item and a grouped field in the same report:

Another way to handle this is to pre-calculate the profit-and-loss rows using Power Query, then build your pivot table from the query result. 

Here's the M-code for the query:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Amount", "AmountOrig"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Amount", each if [Group]="Expense" then [AmountOrig]*-1 else [AmountOrig]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","Income","Profit+Loss",Replacer.ReplaceText,{"Group"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Expense","Profit+Loss",Replacer.ReplaceText,{"Group"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value1",{"Date", "Description", "AmountOrig", "Amount", "Category", "Property", "Unit", "Group"}),
    ProfitAndLoss = Table.RemoveColumns(#"Reordered Columns",{"AmountOrig"}),
    Result = Table.Combine({Source,ProfitAndLoss})
in
    Result

Updated file attached.

queenbean84 

How about based on this transaction sheet?

select regexp2('\/(\d+)\/',Date,1) month,Date,Description,iif(`Group`='Expense',-Amount,Amount) Amount,Category,Property,Unit,'Prifit' `Group` from Add_column_month_row_profit union all
select regexp2('\/(\d+)\/',Date,1) month,* from Add_column_month_row_profit;