Calculate sum based on start and end date

[mansi_luthra12]

Hi All, 

I need to calculate the sum of the amount based on the start and end date for each month.
For example: In the below data TotalAmountPerMonth for Jan is 600 and For Feb it is only adding the active project (those whose end date is in Feb or after Feb). Hence the total amount for Feb is 700(100+200+400). 
If I use TotalYTD it accumulates all the costs from the previous month and doesn't filter the end date 

Thanks and Regards, 

Mansi 

[v-zhangti]

Hi, @mansi_luthra12 

You can try the following methods.
Date table:

Measure = 
CALCULATE ( SUM ( 'Table'[Value] ),
    FILTER ( ALL ( 'Table' ),
        MONTH ( [StartDateScope] ) <= SELECTEDVALUE ( 'Date'[Month] )
            && MONTH ( [EndDateScope] ) >= SELECTEDVALUE ( 'Date'[Month] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mansi_luthra12]

Hi, 
Thanks for the reply the solution is perfectly fine if only one year is present. I have records for multiple years - from the calendar table even if I put a slicer on year it doesn't split the amount. 
for Example : if Jan 2023 was 100 and 2024 Jan is 200 then irrespective of which year is selected the calculation shows 300. 

[v-zhangti · Accepted solution]

Hi, @mansi_luthra12 

Add a year column to the date table and add a year filter to the formula.

Measure = 
CALCULATE ( SUM ( 'Table'[Value] ),
    FILTER ( ALL ( 'Table' ),
        MONTH ( [StartDateScope] ) <= SELECTEDVALUE ( 'Date'[Month] )
            && MONTH ( [EndDateScope] ) >= SELECTEDVALUE ( 'Date'[Month] )
            &&YEAR([StartDateScope])=SELECTEDVALUE('Date'[Year])
    )
)

If you do not get the results you expect, please provide more detailed data.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rubayatyasmin]

Hi, @mansi_luthra12 

here is the example DAX that you can use based on your description. 

TotalAmountPerMonth =
SUMX(
FILTER(
ALL('Table'),
'Table'[StartDate] <= ENDOFMONTH('Date'[Date]) &&
(ISBLANK('Table'[EndDate]) || 'Table'[EndDate] >= STARTOFMONTH('Date'[Date]))
),
'Table'[Amount]
)