cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

 

Thanks in advance for your help !

1 ACCEPTED SOLUTION
Super User III
Super User III

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:

MFelix_0-1604770072103.png

Check PBIX file attach.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

2 REPLIES 2
New Member

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

Super User III
Super User III

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:

MFelix_0-1604770072103.png

Check PBIX file attach.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors