cancel
Showing results for
Did you mean:
Frequent Visitor

## Profit Report

Hi there

I need to build a profit report as I have 3 tables as the following:

A- Table 1 contains:

1- Revenue amount

2- Revenue Date

3- Project ID

4 - Project Name

B- Table 2 Contains:

1- Project id

2- Cost amount

3- Cost Date

C- Table 3 Contains:

1- Project Id

2- Expense amount

3- Expense Date

my objective is to calculate the profit per project based on the time dimension of each table. The issue here is I cant build a relationship based on project ID and Time of each table.

Measures:

Actual Cost =  Cost amount + Expense amount

Profit = Actual Cost - Revenue amount

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## Re: Profit Report

To create another new calculated table as a bridge table, and create relationship between your fact tables.

```Project =
DISTINCT (
UNION (
VALUES ( table1[projectid] ),
VALUES ( table2[projectid] ),
VALUES ( table3[projectid] )
)
)
```
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
4 REPLIES 4
Super User

## Re: Profit Report

I'd recommend to create a Project table as well. That will be connected by the Project ID and the Date table just by the Date columns.

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

Frequent Visitor

Super User

## Re: Profit Report

what exactly should be clarified?

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

Highlighted
Community Support Team

## Re: Profit Report

To create another new calculated table as a bridge table, and create relationship between your fact tables.

```Project =
DISTINCT (
UNION (
VALUES ( table1[projectid] ),
VALUES ( table2[projectid] ),
VALUES ( table3[projectid] )
)
)
```
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)