cancel
Showing results for
Did you mean:
New Member

## How to report revenue per month based on 2 tables: contract register and historical prices

I'm looking for help on this one.

I have 2 tables. One with data about contracts. This includes a unique contract-id, starting & (potential) ending date and a pricing code. It looks like this.

 Contract# Start Date End Date Pricing Code Q 01-04-2019 10-11-2020 A W 01-08-2018 31-12-2999 A E 06-04-2017 31-12-2999 B R 15-11-2019 31-05-2020 B T 15-06-2020 31-12-2999 C Y 01-08-2020 30-09-2020 C

I also have a table which includes the historical prices per pricing code. So, per pricing code you can see the price for a certain period. See below, Pricing code A costs € 100 from 01-01-2018 to 30-06-2019 and it costs € 110 from 01-07-2019 to 31-12-2020.

 Pricing Code Start Date End Date Price A 01-01-2018 30-06-2019 €           100,00 A 01-07-2019 31-12-2020 €           110,00 B 01-01-2017 31-12-2019 €           200,00 B 01-01-2020 31-12-2021 €           205,00 C 01-06-2020 01-12-2021 €           300,00

Can anybody help me how to efficiently connect these 2 tables in Power BI to report the revenue per contract per month.

Something like this:

 Contract# Year Month Revenue Q 2019 4 €           100,00 Q 2019 5 €           100,00 Q 2019 6 €           100,00 Q 2019 7 €           110,00 Q 2019 8 €           110,00 Q 2019 9 €           110,00 Q 2019 10 €           110,00 Q 2019 11 €           110,00 Q 2019 12 €           110,00 Q 2020 1 €           110,00 Q 2020 2 €           110,00 Q 2020 3 €           110,00 Q 2020 4 €           110,00 Q 2020 5 €           110,00 Q 2020 6 €           110,00 Q 2020 7 €           110,00 Q 2020 8 €           110,00 Q 2020 9 €           110,00

1 ACCEPTED SOLUTION
Super User III

Create a calendar table and pricing code table to make the relationship between your to other table and the make the following 2 measures:

``````TotalContract =
VAR Contract_Selected =
SELECTEDVALUE ( Contracts[Contract#] )
VAR dateStart =
MIN ( 'Calendar'[Date] )
VAR dateEnd =
MAX ( 'Calendar'[Date] )
Var Total_Pricing =
CALCULATE (
SUM ( Pricing[Price] ),
Contracts[Contract#] = Contract_Selected,
FILTER (
Contracts,
Contracts[Start Date] <= dateStart
&& Contracts[End Date] >= dateEnd
),
FILTER (
Pricing,
Pricing[Start Date] <= dateStart
&& Pricing[End Date] >= dateEnd
))

return
Total_Pricing

TotalContract_Value =
var CAlendar_Total = SUMMARIZE('Calendar','Calendar'[Year],'Calendar'[Month],"TotalValue", [TotalContract])
var Result = SUMX(CAlendar_Total, [TotalValue])
Return
Result``````

Now just use the measure Total Contract_Value on your visualizations:

Check PBIX file attach.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

2 REPLIES 2
New Member

Hi Miguel, great.... thank you for your help !!!

Super User III

Create a calendar table and pricing code table to make the relationship between your to other table and the make the following 2 measures:

``````TotalContract =
VAR Contract_Selected =
SELECTEDVALUE ( Contracts[Contract#] )
VAR dateStart =
MIN ( 'Calendar'[Date] )
VAR dateEnd =
MAX ( 'Calendar'[Date] )
Var Total_Pricing =
CALCULATE (
SUM ( Pricing[Price] ),
Contracts[Contract#] = Contract_Selected,
FILTER (
Contracts,
Contracts[Start Date] <= dateStart
&& Contracts[End Date] >= dateEnd
),
FILTER (
Pricing,
Pricing[Start Date] <= dateStart
&& Pricing[End Date] >= dateEnd
))

return
Total_Pricing

TotalContract_Value =
var CAlendar_Total = SUMMARIZE('Calendar','Calendar'[Year],'Calendar'[Month],"TotalValue", [TotalContract])
var Result = SUMX(CAlendar_Total, [TotalValue])
Return
Result``````

Now just use the measure Total Contract_Value on your visualizations:

Check PBIX file attach.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!