Get sum of contract amount by month bases on start and end date

[Anonymous]

Hi Guys,

I have some struggels to get the correct results. Hope you can help me out.

There are some contracts for monthly fee. These have a start date, an end date and an amount. I would like to know per month what the total revenue/fee is. When the end date is empty, the contract is open and delivers each month a fee.

I have set up 1 new date tabel which shows each month and create some dax with some comparision with the date, the start date and the end date, but I cannot get the right results.

Revenue =

CALCULATE(

SUM('Contracts'[Amount €]),

FILTER(

ALLSELECTED('Contracts'),

('Revenue'[Date2] >= MIN('Contracts'[Start date]) &&

'Revenue'[Date2] <= MIN('Contracts'[end date]) && NOT(ISBLANK('Contracts'[End Date]))) ||

('Revenue'[Date2] >= MIN('Contracts'[Start Date]) &&

ISBLANK('Contracts'[End Date]))

))

data:

Contract	Company	Start date	end date	amount €
C-001	A	1-8-2021	1-2-2022	100
C-002	A	1-2-2022		150
C-003	B	1-9-2021	1-11-2021	110
C-004	B	1-11-2021	1-1-2022	115
C-004	B	1-1-2022	1-4-2022	120

Expecting result:

1-8-2021	100
1-9-2021	210
1-10-2021	210
1-11-2021	215
1-12-2021	215
1-1-2022	220
1-2-2022	270

pbix file attached: https://1drv.ms/u/s!Akhij5oHG_75nJQw6GNWiLlg1Nv-9A?e=x98k0I

Thanks a lot. Please let me know if you need more info!

[Anonymous]

Hi johnt75,

Thanks for your reply, but this isn't working for me. I would like to have a colum in a table with this information because I want to append this table with another table which has similar data.

[johnt75]

You could create a column on the Revenue table by deleting the MIN() and MAX() from my code so that it is just using the Date column from the current row.

[Anonymous]

Tried that, but DAX isn't excepting the code because it cannot find the [Date2] column. It is only finding this column when using MAX MIN and that kind of functions. Or am I doing something wrong?

Revenue Measure = CALCULATE( SUM(Contracts[amount €]),Contracts[Start date] <=  'Revenue'[Date2] && ( ISBLANK( 'Contracts'[end date]) || 'Contracts'[end date] > 'Revenue'[Date2] ) ) 

[johnt75 · Accepted solution]

Try

Revenue Column = 
var currentDate = 'Revenue'[Date2]
return CALCULATE( SUM(Contracts[amount €]),Contracts[Start date] <=  currentDate && ( ISBLANK( 'Contracts'[end date]) || 'Contracts'[end date] > currentDate ) ) 

[Anonymous]

I should have known that trick with the variable by now...

You are the men! Thank you very much for your help and your patient!

[johnt75]

you can create a new measure

Revenue Measure = CALCULATE( SUM(Contracts[amount €]),Contracts[Start date] <= MIN( 'Revenue'[Date2]) &&
    ( ISBLANK( 'Contracts'[end date]) || 'Contracts'[end date] > MAX('Revenue'[Date2] ) )
)