Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
david2
Helper I
Helper I

Using a date table to create a monthly P&L based on various tables?

Hi all,

 

I'm working with an online painting services company and have built our data model with various tables. I want to create a table in PowerBI that shows our monthly revenue and breakdown of costs, such as in the Excel example below (with fake numbers).

 

Capture.JPG

 

I have tables for each of the line items in my data model (e.g. a table marketingCosts, salaryPayments, allocatedRevenue, etc). These tables each contain one or more date columns. I now want to create a table like the one in the example above, but am not sure how to link the columns from different tables to each other using the same date.

 

I read up on date tables and that seems to be the way forward - can someone explain me how they would approach this situation?

 

Thanks 🙂

 

David

 

Each of the lines would be pulled from a different table 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @david2,

 

According to your description, you want to merge multiple tables and summary these records by type and date,right?

If this is a case, you can refer to below steps to achieve your requirement(Matrix visual).

 

1. Merge tables.(these table's structure must be same)

Table structure.

Capture.PNG

 

Capture2.PNG

 

Table formula:

 

Table = UNION(SUMMARIZE(Sheet1,Sheet1[Type],Sheet1[Date],"Amount",SUM(Sheet1[Amount])),SUMMARIZE(Sheet2,Sheet2[Type],Sheet2[Date],"Amount",SUM(Sheet2[Amount])),SUMMARIZE(Sheet3,Sheet3[Type],Sheet3[Date],"Amount",SUM(Sheet3[Amount]))
)

 

Create matrix visual:

Capture3.PNG

 

Detial result:

Capture4.PNG

 

In addition, you can add a calculate column to show the specific date's records.

 

Calculate column.

Day = DAY([Date])

 

Slicer:

Capture5.PNG

 

Capture6.PNG

 

Regards,

Xiaoxin Sheng

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @david2,

 

According to your description, you want to merge multiple tables and summary these records by type and date,right?

If this is a case, you can refer to below steps to achieve your requirement(Matrix visual).

 

1. Merge tables.(these table's structure must be same)

Table structure.

Capture.PNG

 

Capture2.PNG

 

Table formula:

 

Table = UNION(SUMMARIZE(Sheet1,Sheet1[Type],Sheet1[Date],"Amount",SUM(Sheet1[Amount])),SUMMARIZE(Sheet2,Sheet2[Type],Sheet2[Date],"Amount",SUM(Sheet2[Amount])),SUMMARIZE(Sheet3,Sheet3[Type],Sheet3[Date],"Amount",SUM(Sheet3[Amount]))
)

 

Create matrix visual:

Capture3.PNG

 

Detial result:

Capture4.PNG

 

In addition, you can add a calculate column to show the specific date's records.

 

Calculate column.

Day = DAY([Date])

 

Slicer:

Capture5.PNG

 

Capture6.PNG

 

Regards,

Xiaoxin Sheng

kcantor
Community Champion
Community Champion

@david2

I would create a date dimension table with a column containing all dates (none missing) callde DateKey. I would have the necessary date fields for my company in that table as well including quarter number, year, month, week number, day of week, and day of year. Then I would link all of my fact table to it using the date on the fact tables to the date key in the date dimension table.

Next your would build calculation in dax for your fact tables. The values from those calculations go in the values section with the dates either as columns or rows.

For example, in a budget table I might have

Budget Amount = SUM('BudgetTable'[Amount])

LY Budget Amount = CALCULATE([Budget Amount], DATEADD(DimDate[DateKey], -1, year))

I would put the months on the axis and these two calculations as values to show year over year budget amounts. Create the calculations for Actual Cost and Revenue the same way in the respective tables and add them to your tables or graphs in the same way.

You can then use the measures in more complex measures. Such as if you have a measure for revenue and one for cost you could create:
Profit=[Revenue]-[Cost]

that would assume that Revenue and cost are the measure names.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors