Profit Report

[Anonymous]

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

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

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

[ImkeF]

Hi @Anonymous 

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.

Recommended reading: https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

[Anonymous]

Thanks @ImkeF  for your response.

could you please clarify more 

[ImkeF]

Hi @Anonymous ,

what exactly should be clarified?