Calculate booked amount and to be renewed based on contract dates

[STimon]

Hi,

I'm trying to build a measure to expand a monthly amount between dates, taking into account if its' booked or to be renewed, (that depends on the contract end date)

This is the table with the contract amounts and dates. I dont' care about the day of the month. I've got a contract monthly amount that can change from one month to another; that's why I calculate from monthly amount and not total amount

CONTRACT	START DATE	END DATE	TOTAL MONTHS	MONTHS BOOKED FROM ACTUAL MONTH (INCLUDED)	MONTHLY AMOUNT	AMOUNT BOOKED	LAST MONTH FOR CALCULATIONS	AMOUNT TO BE RENEWED
AAA	01/01/2017	30/09/2022	60	17	5.000,00	85.000,00	01/11/2022	10.000,00
BBB	17/11/2020	17/11/2022	36	19	1.000,00	19.000,00	01/11/2022	0,00
CCC	01/05/2021	30/09/2021	5	5	2.500,00	12.500,00	01/11/2022	35.000,00
						116.500,00		45.000,00

We already have another calendar table from 01 Jan 2021 to last contract date (17 Nov 2022). And I want to build a measure to get something like this:

	2021		2022
	BOOKED	TO BE RENEWED	BOOKED	TO BE RENEWED
JANUARY			6.000,00	2.500,00
FEBRUARY			6.000,00	2.500,00
MARCH			6.000,00	2.500,00
APRIL			6.000,00	2.500,00
MAY	8.500,00		6.000,00	2.500,00
JUNE	8.500,00		6.000,00	2.500,00
JULY	8.500,00		6.000,00	2.500,00
AUGUST	8.500,00		6.000,00	2.500,00
SEPTEMBER	8.500,00		6.000,00	2.500,00
OCTOBER	6.000,00	2.500,00	1.000,00	7.500,00
NOVEMBER	6.000,00	2.500,00	1.000,00	7.500,00
DECEMBER	6.000,00	2.500,00
	60.500,00	7.500,00	56.000,00	37.500,00

Can you help me? I've been looking here for some measures example but I can't get the right formula

Thanks!!

[Anonymous]

Not clear enough to solve. The first table does not represent correctly the problem since you say that the monthly amount for a contract can change; data should always be representative of the problem at hand and this is not reflected in any way in the table, as much as I can judge.

I would kindly suggest reading this first before posting: https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/td-...

To get a solution you'll have to put a bit more effort into the explanation, I'm afraid.

[STimon]

Hi,
The change of the monthly amount doesn't really matter, I probably expressed myself wrong. If was just trying to explain that another solution I've found that comes from a total value doesn't work for me or I don't know how to make it work in my scenary (https://community.powerbi.com/t5/Desktop/How-to-spread-value-between-two-dates/m-p/914942 )

I already have managed to built a table only with the last date of the month. So I've got two tables, one is called Contracts, with all the monthly amounts, contract start date and end date, and another one is called Bookings, with last date of month. I have a PBIX file with this examples:

https://we.tl/t-u8OTZqM8RD 

CONTRACT:

CONTRACT START DATE END DATE MONTHLY AMOUNT
AAA 01/01/2017 30/09/2022 5.000,00
BBB 17/11/2020 17/11/2022 1.000,00
CCC 01/05/2021 30/09/2021 2.500,00

BOOKINGS (goes like this, with all last end day of the month bettween today and last contract end date):

MONTH
31/05/2021
30/06/2021
31/07/2021

What I trying to get is to spread the monthly amount between all of "MONTHS" in table "BOOKINGS" only when the Contract End Date < MONTHS

Something like this is the result I need to get:

MONTH BOKEED TO BE RENEWED
31/05/2021 8.500,00 0,00
30/06/2021 8.500,00 0,00
31/07/2021 8.500,00 0,00
31/08/2021 8.500,00 0,00
30/09/2021 8.500,00 0,00
31/10/2021 6.000,00 2.500,00
30/11/2021 6.000,00 2.500,00
31/12/2021 6.000,00 2.500,00
31/01/2022 6.000,00 2.500,00
28/02/2022 6.000,00 2.500,00
31/03/2022 6.000,00 2.500,00
30/04/2022 6.000,00 2.500,00
31/05/2022 6.000,00 2.500,00
30/06/2022 6.000,00 2.500,00
31/07/2022 6.000,00 2.500,00
31/08/2022 6.000,00 2.500,00
30/09/2022 6.000,00 2.500,00
31/10/2022 1.000,00 7.500,00
30/11/2022 1.000,00 7.500,00

And of course I'll need to filter also for each contract, for example if i filter contract CCC, the result should be like this:

MONTH BOKEED TO BE RENEWED
31/05/2021 2.500,00 0,00
30/06/2021 2.500,00 0,00
31/07/2021 2.500,00 0,00
31/08/2021 2.500,00 0,00
30/09/2021 2.500,00 0,00
31/10/2021 0,00 2.500,00
30/11/2021 0,00 2.500,00
31/12/2021 0,00 2.500,00
31/01/2022 0,00 2.500,00
28/02/2022 0,00 2.500,00
31/03/2022 0,00 2.500,00
30/04/2022 0,00 2.500,00
31/05/2022 0,00 2.500,00
30/06/2022 0,00 2.500,00
31/07/2022 0,00 2.500,00
31/08/2022 0,00 2.500,00
30/09/2022 0,00 2.500,00
31/10/2022 0,00 2.500,00
30/11/2022 0,00 2.500,00

[STimon · Accepted solution]

Ok, I figured it out!

Maybe it's a bit rough 😁 but If no one find another easy solutions I'll marked it lately as solved, in case it helps anyone.

I created a crossjoin table (RESULT): 

RESULT = CROSSJOIN(BOOKING,SELECTCOLUMNS(CONTRACT,"CONTRACT",CONTRACT[CONTRACT],"END DATE",CONTRACT[END DATE].[Date],"MONTHLY AMOUNT",CONTRACT[MONTHLY AMOUNT]))

And inside this table two calculated columns:

BOOKED = IF(or(RESULT[END DATE]>RESULT[END OF MONTH],ENDOFMONTH(RESULT[END DATE].[Date])=RESULT[END OF MONTH]),RESULT[MONTHLY AMOUNT],0)

TO BE RENEWED = 

TO BE RENEWED = IF(ENDOFMONTH(RESULT[END DATE].[Date])<RESULT[END OF MONTH],RESULT[MONTHLY AMOUNT],0)

And from this table a Matrix visual and filter works nicely:

The final pbix: https://we.tl/t-EHJgR7LxSO