Dashboard on Revenue by Rental Agreement

[Ros_Al]

Hi 

I'm new to Power Bi, may I know what kind of tables must I have to calculate the revenue by year.

Do I need the calendar columns, Customer Table, Sales table (rental amount), Contract ID table?

How do I calculate the revenue by year?

Customer	Contract Start	Contract End	Rental Per Month
Cus A	01/10/2017	30/09/2019	3500
Cus A	01/10/2019	30/09/2022	3800
Cus A	01/10/2022	30/09/2024	4200
Cus B	01/01/2020	31/12/2020	270
Cus B	01/01/2021	31/12/2022	270
Cus C	01/10/2019	31/03/2020	2800
Cus D	15/04/2017	14/04/2019	1200
Cus D	15/04/2019	14/04/2021	1400
Cus D	15/04/2021	14/04/2023	1300
Cus E	01/12/2019	30/11/2020	650
Cus E	01/12/2020	30/11/2021	400
Cus F	01/01/2020	31/12/2020	700
Cus F	01/01/2021	31/12/2021	750
Cus G	01/10/2020	30/09/2021	400
Cus G	01/10/2021	30/09/2022	450

[v-yetao1-msft · Accepted solution]

Hi @Ros_Al 

For the twelve months from [Contract Start] , you can create your own date table and calculate the annual rent separately .

Create a calculated table .

date = GENERATE( SUMMARIZE( ADDCOLUMNS( CALENDAR(MIN('Table'[Contract Start]),MAX('Table'[Contract End])),"year",YEAR([Date])),[year]),VALUES('Table'[Customer]))

Then create a measure to return the revenue by year .

Measure = 
// CALCULATE(SUM('Table'[Rental Per Month]),
// FILTER('Table',YEAR([Contract Start])<=MAX('date'[year])&&YEAR([Contract End])>=MAX('date'[year])&&[Customer]=MAX('date'[Customer])))
var _startmonth=CALCULATE(MONTH(MAX('Table'[Contract Start])),FILTER('Table',[Customer]=MAX('date'[Customer])))
return IF(_startmonth=1,
CALCULATE(SUM('Table'[Rental Per Month])*12,FILTER('Table',[Customer]=MAX('date'[Customer])&&YEAR([Contract Start])<=MAX('date'[year])&&YEAR([Contract End])>=MAX('date'[year]))),
CALCULATE(SUM('Table'[Rental Per Month])*12,FILTER('Table',[Customer]=MAX('date'[Customer])&&YEAR([Contract Start])<=MAX('date'[year])&&YEAR([Contract End])-1>=MAX('date'[year]))))

 You will get a result like this :

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Understand star schema and the importance for Power BI … (bing.com)