Sum SQM by the contract with stard and end date

[andrebsilva]

Hi

I need a help to create a measure to sum a sqm by contract accoding the contract term
I have a contract base and create a calendar table with calendarauto

Contract	Start Date	End Date	SQM
BRA0001	01/01/2015	12/31/2018	6.000,00
BRA0001	01/01/2019	12/31/2021	2.500,00
BRA0002	01/07/2016	06/30/2020	1.500,00

I expect to present the information like this

FY	Q1	Q2	Q3	Q4
2015	6.000,00	6.000,00	6.000,00	6.000,00
2016	6.000,00	6.000,00	7.500,00	7.500,00
2017	7.500,00	7.500,00	7.500,00	7.500,00
2018	7.500,00	7.500,00	7.500,00	7.500,00
2019	4.000,00	4.000,00	4.000,00	4.000,00
2020	4.000,00	4.000,00	1.500,00	1.500,00
2021	1.500,00	1.500,00	1.500,00	1.500,00
2022	0	0	0	0

Best Regards

[andrebsilva1]

@tamerj1 
Thanks a lot for your answer.

The solution works, but now I have another probleme.
The sum by month or quarter not works well, when the total of sqm change in the middle of month

Table fato

The measeure

CumulativeSQM =

VAR MaxDate = MAX ('Calendar'[Date])

VAR MinDate = MIN ('Calendar'[Date])

RETURN

Calculate (

    sum(Floor_Level_Data[Contracted Sq M]),

        Floor_Level_Data[OCD] <= MaxDate,

        Floor_Level_Data[LED] >= MinDate

)

Q3 of FY21, presente a total that not exist 1.667,63.

I try to resolve change sumx by calculete, but not sucess until now.

Note my fiscal year star on setember.

Best Regards,

André
Notes - I don't now my first user not works, so I create a new user.

[tamerj1]

Hi @andrebsilva 

Please try

=
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR MinDate =
MIN ( 'Date'[Date] )
RETURN
SUMX (
FILTER (
'Table',
'Table'[Start Date] <= MaxDate
&& 'Table'[End Date] >= MinDate
),
'Table'[SQM]
)

[andrebsilva]

@tamerj1 @amitchandak @Greg_Deckler 
Please, could you help me?