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

[david2]

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).

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 

[v-shex-msft · Accepted solution]

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.

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:

Detial result:

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

Calculate column.

Day = DAY([Date])

Slicer:

Regards,

Xiaoxin Sheng

[kcantor]

@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.