Forum Discussion
Calculate sum based on start and end date
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.
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.
Community SupportHi, 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.