Create a dynamic monthly income report

Question

Create a dynamic monthly income report

Anonymous

I have a large database and need to extract data to create a dynamic monthly Income Report by customer and six different payment sources by date. What is the the best method to use for this. I have tried sumif but the formulas are too large and not dynamic. So if the data is changed on the source data the complex formulas all have to be updated which is unworkable. I am including a simplified example of what I am trying to accomplish. The data base is much more complex but I am showing only the columns which are pertinent to create this report. Any help is appreciated.

	Payment Received
	Source 1	Source 2	Source 3	Source 4	Source 5	Source 6
	Date	$	Date	$	Dlate	$	Date	$	Date	$	Date	$
Jack	1/5/24	100	2/8/24	50	3/6/24	80	6/1/24	50	7/1/24	25	8/1/24
Jack	2/5/24	45			1/28/24
Jack	4/25/24	70	4/25/24	90					5/30/24	100
Jack	8/2/24	200					7/6/24	150
Martha	1/5/24	100	2/8/24	50	3/6/24	80	6/1/24	50	7/1/24	25	8/1/24
Martha	2/5/24	45			1/28/24
Martha	4/25/24	70	4/25/24	90					5/30/24	100
Martha	8/2/24	200					7/6/24	150




Final Income Report

	Jan	Feb	Mar	Apr	May	Jun	July	Aug	Sept	Oct	Nov	Dec	Total
Jack
Martha
Total

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Answer 1

Ashish Mathur 101.9K Volunteer Moderator

You are welcome. Simply build a Pivot table from the green range.

0 comments

Answer 2

Anonymous

Thanks for the answer. I have not used power query but will try. Once it is in a new table format how do I apply that to a table to get my final income report?

Any help appreciated, thanks.

0 comments

Answer 3

Thanks for the answer. If I can get the data into a tabular format how do I get the data from that into a pivot table? Also, when I said the data table is more complex I should have said that it has other parts which do not affect the payment received section. I am only trying to convert the payment received section into a monthly sales report.

Any help is appreciated. Thanks.

Answer 4

Hi,

I used this M code in Power Query to transform your data into a proper table

let

Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], 

#"Extract headers" = Table.Transpose(Table.FirstN(Source,2)), 

#"Filled Down" = Table.FillDown(#"Extract headers",{"Column1"}), 

#"Proper headers created" = Table.AddColumn(#"Filled Down", "Custom", each Text.Combine({[Column1],[Column2]}," "))[[Custom]], 

#"Recreate table with proper headers" = Table.PromoteHeaders(Table.Transpose(#"Proper headers created")&Table.Skip(Source,2)), 

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Recreate table with proper headers", {"Name"}, "Attribute", "Value"), 

#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Source", "Parameter"}), 

#"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 1, 1, Int64.Type), 

#"Grouped Rows" = Table.Group(#"Added Index", {"Name", "Source", "Parameter"}, {{"Count", each Table.AddIndexColumn(\_,"Index1",1,1), type table [Name=text, Source=nullable text, Parameter=nullable text, Value=any, Index=number, Index1=number]}}), 

#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Value", "Index", "Index1"}, {"Value", "Index", "Index1"}), 

#"Sorted Rows" = Table.Sort(#"Expanded Count",{{"Index", Order.Ascending}}), 

#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}), 

#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Parameter]), "Parameter", "Value"), 

#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}), 

#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Name", type text}, {"Source", type text}, {"Date", type datetime}, {"$", Int64.Type}})

in

#"Changed Type"

The transformed table looks like this

Answer 5

riny 20,870 Volunteer Moderator

Providing a simplified example when your real data is "much more complex" will lead to us here proving a simplified solution that will not work with the real data source. I would recommend you to look into Power Query so that you can transform the data from this:

into a tabular format like this:

So that you can create a pivot table like this:

0 comments

Share via

Create a dynamic monthly income report

8 answers