Forum Discussion

mansi_luthra12's avatar
mansi_luthra12
New Member
2 years ago
Solved

Calculate sum based on start and end date

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 th...
Need Help
Show and Tell
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    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.

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
2 years ago

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's avatar
    mansi_luthra12
    New Member
    2 years ago

    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's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      2 years ago

      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.