Cumulative total for month

[kkanda]

Hi All,

I have run into a problem I cannot resolve. I have set of dates, Target and Actual numbers in a table. I need to calculate the cumulative total for each day of the month. The logic appears to be simple enough, but it doesn't work. Here is the data:

DATE            TARGET ACTUAL RT_mnth st_day_mnth  last_day_mnth

1/1/2023	27	38	38	1/1/2023	1/31/2023
1/2/2023	39	20	20	1/1/2023	1/31/2023
1/3/2023	26	37	37	1/1/2023	1/31/2023
1/4/2023	36	24	24	1/1/2023	1/31/2023
1/5/2023	18	23	23	1/1/2023	1/31/2023
1/6/2023	23	19	19	1/1/2023	1/31/2023
1/7/2023	14	23	23	1/1/2023	1/31/2023
1/8/2023	36	10	10	1/1/2023	1/31/2023
1/9/2023	14	40	40	1/1/2023	1/31/2023
1/10/2023	10	12	12	1/1/2023	1/31/2023
1/11/2023	36	25	25	1/1/2023	1/31/2023
1/12/2023	20	20	20	1/1/2023	1/31/2023
1/13/2023	10	37	37	1/1/2023	1/31/2023
1/14/2023	36	10	10	1/1/2023	1/31/2023
1/15/2023	40	29	29	1/1/2023	1/31/2023

'st_day_mnth' and 'last_day_mnth' are calculate measures from DATE. 'RT_mnth' is the cumulative total of ACTUAL column and has the following expression:

RT_mnth = CALCULATE(

    SUM(TestSh[ACTUAL]), FILTER(TestSh,

        TestSh[DATE]>= [st_day_mnth] && TestSh[DATE] <= [last_day_mnth])

)

I tried ALL and ALLEXCEPT functions but it does not give me the desired result. I have also tried SUMX with the table name and the column ACTUAL, but there is no change. 

Please suggest a suitable change to my code so that I get the cumulative total up to the date in the DATE column for each month. One solution is to change this to a date table and apply DATEMTD. But I want to understand why it is not working. 

Please suggest where I am going wrong. 

[kkanda]

Thank you @Fowmy... it worked. I tried STARTOFMONTH function for __startDate. That has worked also. 

[Fowmy · Accepted solution]

@kkanda 

Use this measure:

RT_mnth =
VAR __StartDate =
    EOMONTH ( MAX ( TestSh[DATE] ), -1 ) + 1
VAR __LastDate =
    MAX ( TestSh[DATE] )
RETURN
    CALCULATE (
        SUM ( TestSh[ACTUAL] ),
        TestSh[DATE] >= __StartDate,
        TestSh[DATE] <= __LastDate
    )