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 !

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

[Rickmaurinus]

Hi Robert,

The measure provided does not take into account contracts on a total level in your matrix. Whereas on a detail level the contracts are already filtered. Hence the difference in calculated numbers. 

Instead you could try: 

TotalContract_Value = 
VAR ContractCalendarCombinations =
    ADDCOLUMNS(
        CROSSJOIN(
            VALUES( 'Calendar'[Year-Month]),
            VALUES( Contracts[Contract#] )
        ), 
        "ContractValue", [TotalContract] 
    )
VAR Result = SUMX(ContractCalendarCombinations, [ContractValue] )
RETURN
Result

Best regards,

Rick de Groot

https://gorilla.bi

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

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

[RobertZijlstra]

Hi @MFelix ,

I'm still struggling with this... the total amount of Contract Value is incorrect.
If you take a look at the value per contract is looks fine. In your example:

Q = € 4.860
W = € 2.060
E = € 1.225
R = € 1.800
T = € 2.580
Y = € 600

If you add these numbers the total value must be € 13.125, but in the matrix in Power BI it shows a total of € 9.240.

Can you help me?
Thanks in advance.
Best regards, Robert

[MFelix · Accepted solution]

Hi  @RobertZijlstra ,

this has to do with context do the following, create a new measure to use on your calculation:

Total_Contract_Values = SUMX(VALUES(Contracts[Contract#]), [TotalContract_Value])

Use this measure in your visualizations.

Result below and in attach file. 

[RobertZijlstra]

Thanks again Miguel, but I am still struggling with an issue.

With these DAX formulas there is no revenue on contracts which start or end during the month. In my example contract Q ends on 10-11-2020, so I would like to calculate 10/30 of the monthly price. And contract E starts on 06-04-2017, so I would like to calculate 24/30 of the monthly price.

I tried to add something in the DAX formula that would take these exceptions (start or end on a random date during the month) into account.

I am sorry to ask you again, but it would be great if you can help me out again.

Best regards,
Robert