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

[RobertZijlstra]

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

Thanks in advance for your help !

[RobertZijlstra]

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

[MFelix · Accepted solution]

Hi @RobertZijlstra ,

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.