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 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 

 

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.

4 Replies

  • 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.

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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]
    )