Sorry my bad in the demo.
The problem I have as per original question is that the formula doesn’t take into account the number of days in the month where a contract is valid
I’'ve tried the following:
Contracts =
VAR dayrate = [Contract Revenue in Progress]
VAR days = CALCULATE( COUNTROWS( 'Event Dates'),
FILTER( VALUES(Contracts[Date On]), Contracts[Date On] <= MAX('Event Dates'[Date])),
FILTER( VALUES( Contracts[Date Off]), Contracts[Date Off] >=MIN('Event Dates'[Date])))
return
dayrate*days
Nov should equal 16 days x £1.64 as the only contract valid in Nov was from 15th Nov - 30th Nov
but this gives me the sum of the day rate * # days in the month rather than the sum of the day rate only taking into account when the contract is valid
Dec s/be 31days x 1.64
Jan s/be sum of 31 days x £.164 + 26days x £27.40 = £763.24
How can I get the formula to take into consideration the On Off dates for each contract when looking by Month and Year
Thanks for you help so far